0

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

2
  • 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. 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
    – seaandtea
    Oct 21, 2020 at 15:46

2 Answers 2

1

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"),""))

enter image description here

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,"")))

enter image description here

4
  • this is exactly what I needed. I can't thank you enough!!! Your help is very appreciated.
    – seaandtea
    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?
    – seaandtea
    Nov 3, 2020 at 15:31
  • Just updated the formula - let me know if that's work for you. Nov 3, 2020 at 15:57
  • that works! Thanks so much
    – seaandtea
    Nov 3, 2020 at 17:04
0

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

1
  • 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)
    – seaandtea
    Oct 21, 2020 at 15:51

Your Answer

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

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