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”.
=IF([YourDateField]=0, "Not specified", "Field value: " & TEXT([YourDateField], "dd.MM.yyyy"))