0

I have a sheet that contains:

  • column A: names
  • column B: date of admission
  • column C: date of leave
  • column D: date of death
  • column Z: date of birth

I want to calculate the duration of stay (date of leave - date of admission) if date of death is empty, and (date of leave - date of death) if there is a date of death.

I have this equation which calculates age:

=DATEDIF($Z5,TODAY(),"Y") & " Years, " & DATEDIF($Z5,TODAY(),"YM") & " Months, " & DATEDIF($Z5,TODAY(),"MD") & " Days"

Can it be modified to satisfy my need, or is there another formula to use?

Improve this question
1
  • You might want to check out Chip Pearson's website. He has some pretty nifty formulas for date calculations. cpearson.com/excel/MainPage.aspx Commented Jan 31, 2016 at 6:23

1 Answer 1

Reset to default
0

The following formula gives you requested result with durations expressed in number of days.

=IF($D5='',$C5 - $B5, $C5 - $D5)

UPDATE: If you want to express the date intervals in years, months and days following the logic in your question, the formula is going to be more verbose:

=IF($D5='', DATEDIF($B5, $C5, "Y") & " Years, " & DATEDIF($B5, $C5, "YM") & " Months, " & DATEDIF($B5, $C5, "MD") & " Days", DATEDIF($D5, $C5, "Y") & " Years, " & DATEDIF($D5, $C5, "YM") & " Months, " & DATEDIF($D5, $C5, "MD") & " Days")

In case you are going to use date intervals expressed in years, months and days more often in your documents, it is worth doing calculation in smaller steps in neighboring cells or creating user defined function which will help you to maintain the document further. Just google "user defined function excel" - any of top links provide very good explanations for this.

Improve this answer
8
  • If I had posted this answer, it would have been downvoted. Commented Jan 31, 2016 at 6:24
  • The OP obviously knows how to find date differences and provided his current formula for age showing how he wants the difference expressed. The question is how to adapt that formula to include the logic you used here. Can you improve your answer to address the question? Commented Jan 31, 2016 at 6:44
  • Thank you, but I need to display the duration of stay in years, moths, and days, not just days Commented Feb 1, 2016 at 7:56
  • 1
    @hany tx - I'll review it today to make sure it correctly answers original question. Of course it can be modified to make it's logic even more elaborated but it would become hardly readable. I recommend that you either create user defined function for date difference in human readable text or use several hidden cells next to final result for doing calculation in small understandable steps. I suggest that you use latter approach - it is much easier for beginners Commented Feb 2, 2016 at 12:23
  • 1
    @Hany I have read your original question and the last comment and I have found my formula correctly answers your original question as you are asking to find a difference between date of leave (i.e. column C) and date of death (i.e. column D) if there is a date of death. Commented Feb 8, 2016 at 11:13

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.