# Calculated Column Question Blank Dates

I have been stumped on this particular task for weeks. Any help would be greatly appreciated.

I have three columns I am working with: Date Admitted to Hospital (date column), Date Discharged (date column), and Hospital Duration (calculated column)

Hospital Duration currently looks like this:

``````=IF(ISBLANK([Date Discharged]),DATEDIF([Date Admitted to Hospital],TODAY(),"d"))
``````

This is great because it accounts for a scenario where 'Date Admitted to Hospital' is filled in, but 'Date Discharged' is blank. However, it does not account for when neither of the columns are filled out - and when BOTH of the columns are filled out, the result I get in 'Hospital Duration' is "no"??

• What do you want to see in [Hospital Duration], [Date Admitted to Hospital]-[Date Discharged] or [Date Admitted to Hospital]-TODAY()?. Best if you could put out an excel data the output that you want to see. I can help. Commented Oct 21, 2020 at 15:18
• Thank you so much. If both or none of the columns are filled in, I would like [Date Admitted to Hospital]-[Date Discharged]. If [Date Discharged] is missing, I would like to use [Date Admitted to Hospital]-TODAY(), this avoids getting the #NUM! or #NAME? error in SharePoint Commented Oct 21, 2020 at 15:46

Based on your requirements..I'm posting the below.

``````=IF(AND(NOT(ISBLANK(AdmitDate)),NOT(ISBLANK(DischargeDate))),DATEDIF(AdmitDate,DischargeDate,"d"),IF(AND(NOT(ISBLANK(AdmitDate)),ISBLANK(DischargeDate)),DATEDIF(AdmitDate,TODAY(),"d"),""))

``````

Updating the formula to adjust the new requirements

If both AdmitDate and DischargeDate are blank, is there a way to show the HospitalStay column as 0

``````=IF(AND(NOT(ISBLANK(AdmitDate)),NOT(ISBLANK(DischargeDate))),DATEDIF(AdmitDate,DischargeDate,"d"),IF(AND(NOT(ISBLANK(AdmitDate)),ISBLANK(DischargeDate)),DATEDIF(AdmitDate,TODAY(),"d"),IF(AND(ISBLANK(AdmitDate),ISBLANK(DischargeDate)),0,"")))
``````

• this is exactly what I needed. I can't thank you enough!!! Your help is very appreciated. Commented Oct 21, 2020 at 16:20
• Hi Ganesh, I had some new requirements come up and i was hoping you could help me with this again. If both AdmitDate and DischargeDate are blank, is there a way to show the HospitalStay column as 0? Commented Nov 3, 2020 at 15:31
• Just updated the formula - let me know if that's work for you. Commented Nov 3, 2020 at 15:57
• that works! Thanks so much Commented Nov 3, 2020 at 17:04

If I understood your requirements correctly (correct me if I am wrong), this is what you are looking for:

``````IF(ISBLANK([Date Discharged]),DATEDIF([Date Admitted to Hospital],TODAY(),"d"),DATEDIF([Date Admitted to Hospital],[Date Discharged],"d"))
``````

Microsoft official documentation:

Calculate the difference between two dates

• this is a lot closer, thank you so much. Is there a way to add in when both fields are blank? If both fields are blank, the column should be zero. (right now, if both columns are blank, it is using today's date and the number calculated in 44,000) Commented Oct 21, 2020 at 15:51