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:
An empty date is a FALSE value, so you could also write:
=IF( [Due Date] , TEXT( [Due Date] , “MMMM YYYY”) ,”no date” )
Tried both with 2010 and it would not work in my calculated field formula, kept getting error with;
=IF(ISBLANK([CS Suspense Date]),””,TEXT([CS Suspense Date],”dd MMM yy”))
or
=IF([CS Suspense Date],TEXT([CS Suspense Date],”dd MMM yy”),”no date”)
That is strange indeed. The syntax definitely looks to be correct. I’d make sure that the quotes are not the “fancy” quotes (which you sometimes get when you copy/paste from the web), and replace them with “straight” quotes if that is the case.
I’ve been doing something similar in Sharepoint to leave blank a total field if all of the summed fields are blank. The output is a currancy field and the formula is =IF(AND(ISBLANK(Comm2015),ISBLANK(Fee2015)),””,(Comm2015+Fee2015)).
Sharepoint is fine with that, but when I look at the results in Access 2013, it shows the calculated fields as #Error. Have you by chance seen that before?
Hi Elena, I’m not that versed on Access formulas, but it wouldn’t surprise me if the syntax was slightly different across the two applications.
Thanks .It helped me save a lot of time
Worked well, Thank you for posting… You truly help me solve a problem today.
I am so thankful I looked this up. Thank you so much