1

I am getting frustrated with a formula I am trying to use in SharePoint. My formula is to calculate # of days hospitalized. I am wanting to calculate the number of days hospitalized via calculating admit date from both today and the discharge date. In other words, If the Discharge date is blank, calculate Admit date from today and if discharge date is entered, admit date from discharge date. I have this set up in Excel and it is working fine. When I translate it over to SharePoint, it gives me a syntax error.

Excel formula –

IF(ISBLANK(DISCHARGE DATE), TODAY-ADMIT DATE, DISCHARGE DATE-ADMIT DATE)
  • Just so you know, this isn't going to update dynamically. The Today value is only going to update when the item is edited. – Eric Alexander Nov 24 '15 at 16:22
  • Currently have a formula in calculating # of days hospitalized from today's date to Admit Date and it is calculating without editing the items. I used the "hack" that is out there where you create a column titled Today and then go back to your calculated column enter in the formula selecting the Today column. After finishing the formula, you delete the Today column and the calculations are dead on. – S_Wade14 Nov 24 '15 at 16:39
1

As Eric said in the comments this is not going to work.

Your syntax error can be fixed (provided you use SP2013 which has a Today() function) with:

 =IF(ISBLANK([DISCHARGE DATE]), TODAY()-[ADMIT DATE], [DISCHARGE DATE]-[ADMIT DATE])

or shorter:

 =IF([DISCHARGE DATE] , [DISCHARGE DATE] , TODAY() )  -  [ADMIT DATE]

since a blank date is a False value

But that Today function is only correct when the Item is updated today
By either an update of the item or on update of the Formula (which updates all items)

If the item is not updated the Today() result will be the same value as the Modified date.

And that applies to the Today Hack as well... It never worked!!

Today calculations in a Calculated Column

I wrote a long post about workarounds here: How to use Today and Me in Calculated column

And many more:
https://sharepoint.stackexchange.com/search?tab=newest&q=user%3a32871%20today

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.