calculate the difference between 2 dates

[Hany0]

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?

[Beattle Juice]

i am a newb at some of this i cant seam to get my questions right for anybody to understand most the time and i dont know enough on most of this to understand what others are asking but if you can post what you have or are looking for might help i will attach a sheet i was working on with some date junk i was going to use to figure birthdays age and how old a person is or would be if still alive if that will help you

[Beamer]

In cell F2:

Code: Select all

=IF(D2="",C2-B2,D2-B2)

[Hany0]

Thank you, but I need the duration of stay to be in years, months, and days; not just days

[Beamer]

ok, try this in F2:

Code: Select all

=DATEDIF($B2,MAX(C2,D2),"Y") & " Years, " & DATEDIF($B2,MAX(C2,D2),"YM") & " Months, " & DATEDIF($B2,MAX(C2,D2),"MD") & " Days"

Edit: Of course, this formula is running on the assumption that if Leave or Death has a date then the other will be blank.
Edit2: And also, I (maybe incorrectly) assumed that where in your original post you had "(date of leave - date of death)" you actually meant "(date of death - date of admission)".
Edit3: Uploaded new file with formula in case those last 2 assumptions of mine were wrong

[Hany0]

Thank you, this answers my question. Yes I meant (date of admission - date of death) and (date of admission - date of leave if date of death is empty).

I also need to calculate the duration of stay, if both date of death and date of leave are empty, to be (date of admission - today)

[Beamer]

Thankyou for the rep....much appreciated

Try this in cell F2:

Code: Select all

=DATEDIF($B2,MIN(C2,D2,TODAY()),"Y") & " Years, " & DATEDIF($B2,MIN(C2,D2,TODAY()),"YM") & " Months, " & DATEDIF($B2,MIN(C2,D2,TODAY()),"MD") & " Days"

[Hany0]

Thank you very very much, this is exactly what I need.

[Beamer]

You are most welcome, very glad I could help.