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. – Matiur Rahman Oct 21 '20 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 – Torybee Oct 21 '20 at 15:46
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. – Torybee Oct 21 '20 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? – Torybee Nov 3 '20 at 15:31
  • Just updated the formula - let me know if that's work for you. – Matiur Rahman Nov 3 '20 at 15:57
  • that works! Thanks so much – Torybee Nov 3 '20 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) – Torybee Oct 21 '20 at 15:51

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.