Office 365 SharePoint

SharePoint calculated column formatting a date that is null

SharePoint calculated column formatting a date that is null

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:

SharePoint calculated column formatting a date that is null

 

 

 

(Visited 2,497 time, 1 visit today)

6 Comments

Click here to post a comment

  • 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?

About Me

Wendy Neal

Wendy Neal

I am a .NET SharePoint Developer for DMI. I've worked with SharePoint since 2007. I love to share my passion for SharePoint and Office 365 by speaking at various industry and user group events, as well as writing articles for various publications and this blog.   Read More

TwitterCounter for @SharePointWendy
MCSA Office 365
Top 50 SharePoint Blogs

Buy My Book

Archives

  • 2017 (1)
  • 2016 (8)
  • 2015 (23)
  • 2014 (20)
  • 2013 (22)
  • 2012 (15)
  • 2011 (13)