A few weeks ago one of my blog readers sent me a question regarding SharePoint calculated columns. He was trying to format a date column and it was working fine as long as the date wasn’t blank, or null. However when the date was null, he was getting some unexpected results.
I thought I would share the solution here on my blog in case anyone else runs into a similar problem. Following is the original question I received:
I was hoping you could help me with a calculated column condition. I have a Project list, and using calculated column to return the start and delivery dates to show “MMMM YYYYY” so if a delivery date is set to 6/30/2015 it’s displayed as “June 2015”. I created a new column called “New Delivery Date” to record if a project is behind schedule we will keep the Planned Delivery date is missed we still have it on record how far it’s behind.
The problem I have is the calculated column for New Delivery Date is returning “December 1899” since the column its referring to is blank. I would like to create an IF statement that says “IF New Delivery Date = date return MMMM YYYY if not, show as blank.” Could you assist with this?
After a couple emails back and forth to get more info and to determine the name of the column that the calculated column is referring to (which is New Completion Date), I came up with the following formula, and the reader verified that it worked:
=IF(ISBLANK([New Completion Date]),””,TEXT([New Completion Date],”MMMM YYYY”))
Basically what the formula is saying if you break it down into parts is this: