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)
Improve this question
2
  • Just so you know, this isn't going to update dynamically. The Today value is only going to update when the item is edited. Commented Nov 24, 2015 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. Commented Nov 24, 2015 at 16:39

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.