4

In a view of a document library I have the field "Modified" and this is showing only dates but no time. I would like to display the time aswell.

From my understanding it is ill advised to alter this field OOTB, but rather to create a calculated field and format it the way I want.

I have checked the column properties for this Modified-field, and it is set to "Date and time", which is why I find it curious that it only displays the date. In any case I have created another (calculated) field which I am attempting to format the way I want, but with not luck.

Under the Formula section of this new calculated field I am attempting to format "Modified" like this:

=TEXT([Modified],"dd.MM.yyyy hh:mm")

I want the result to appear like this 14.10.2014 15:59. However, when trying to save the above formula I get the "Sorry, something went wrong" page and the mention that the syntax is probably wrong. I am unable to see any obvious errors with the syntax and have tried a multitude of approaches - like removing the "", removing [] from modified and so forth.

I need the field to display time aswell and I am not sure if this is the best approach either. Anyone got any tips to how I could do this? Perhaps the calculated field approach isn't the best either?

  • Are you using SharePoint 2013? If so, have you tried switching the Modified column on your list from Friendly Display Format to Standard? – Jeff Freeman Oct 14 '14 at 19:02
  • I am using Sharepoint 2013, and changing from friendly format to standard in itself does nothing but change the format from "15 nov" to "15.10.2014". However, I noticed that the time format under regional settings was set to 12 hour. Changing to standard format AND time format to 24 hours yields the result I wanted. Thank you for your help. – user3463093 Oct 15 '14 at 7:33
7

The point is that formula expression is usually language dependent.

For example, is site locate is set to 1033 (English - United States), then the formula you have provided in the question is absolutely correct:

Syntax: TEXT(value,format_text) Source

But let's say the site locate is set to 1049 (Russia), in that case the proper syntax would be:

ТЕКСТ(значение;формат)

What matters here is delimiter, as you can see it is ; for 1049 locale and not , like in case of 1033 locale.

Solution

  1. Identify the locate of SharePoint site (go to Regional Settings - see the figure 1)
  2. Go to A list of available functions and verify the proper syntax for a function according to the language specified (see figure 2 and 3)

Figure 1. How to identify Locale settings enter image description here

Figure 2. TEXT formula for 1049 locale (e.g. 1049 locale) enter image description here

Figure 3 How to determine formula syntax

enter image description here

Update

How to set Time Format enter image description here

References

  • 2
    Thank you for this well explained post, it definitely pointed me in the right direction. I might not need the calculated field after all. I changed the field to show standard format instead of friendly format. Also (thanks to you) I looked into the regional settings and noticed that for the site it was set to 12-hour format. Changed this to 24-hour and the field now shows as I want it to. I was wondering though, creating new sites and subsites the standard is still 12-hour even if I change it on the root site collection. Do you know of any way to change default to 24-hour and standard-mode? – user3463093 Oct 15 '14 at 7:40
  • Yes, it is doable, by enabling "All sub-sites inherit these Regional Settings" will apply the changes, including Time format to all sub sites (see the updated answer) – Vadim Gremyachev Oct 15 '14 at 8:02
0

Check your Site Columns definition; when I look at a Modified column in a document library, I see date and time.

Either Site Column or Site Content Type (I believe Site Column is the driver), check your params.

Column Name: Date Modified (from Existing Core Document Columns) 
In additional column settings:    
     Date and Time Format: **Date and Time**

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.