Second Life of a Hungarian SharePoint Geek

December 5, 2009

Displaying dates in calculated columns in date format

Filed under: Calculated columns, SharePoint — Tags: , — Peter Holpar @ 01:54

When we have to use a DateTime field in a text-type calculated column (for example, appending the creation date after the „Created on” text), and would like to create the formula like

="Created on" & Created

then we might be a bit surprised on the result, as there will be a long decimal number appended to the text instead of the creation date. What’s that?

To make computing easier SharePoint stores dates as numbers, where 1 means 31.12.1899 and 39996 means 2.6.2009. The earliest date one can set on the SharePoint user interface is 1.1.1900.

Storing time values is even more interesting. That is the decimal fraction of the decimal number, where 0 means midnight, 0.5 means noun, and 22:54 is stored as 0,9544791667. So the decimal number mentioned above is the count of the days after 30.12.1899.

So far so good, but we don’t have the answer for the original question yet, how can we use this number that represents a date append to the text as date.

Fortunately, the TEXT function in calculated columns provides a solution for that. This function expect the first parameter to be a numeric value, just the one we have for the date, and a text pattern to format the numeric value. We can use the patterns similar to the ones for the .NET DateTime type, for example:

="Created on: " & TEXT([Created], "dd.MM.yyyy")

Well, that is much better so. But what happens when we have to work with a mandatory field, like the creation date, but a DateTime value that might be empty?

The 0 value is reserved just for this case, and 30.12.1899 will be displayed. That is not what we need, but what we should do exactly instead of appending this date, depends on the business needs. You can left the column empty or display another text, like „Not specified”.

For example:

=IF([YourDateField]=0, "Not specified", "Field value: " & TEXT([YourDateField], "dd.MM.yyyy"))

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: