| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time between mulitple dates
Question
Let's take for example a Hire-date of 10/16/06 in cell A2 =DATEDIF(A2,NOW(),"y")&" years "&DATEDIF(A2,NOW(),"ym")&" months " and I get 2 years and 10 months but now I have 3 dates... Hire-date, Term-date, Rehire-date Is there a forumula that would calculate the years and months in between? For example Hire-date = 10/23/06 Term-date = 08/02/08 Rehire-date = 04/06/09 I was able to calculate the months using the forumula =(DATEDIF(B3,C3,"M")+DATEDIF(D3,NOW(),"M")) = 25 but I am having some trouble changing the 25 into 2 years 1 month Thanks, PJS |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating time between mulitple dates
Try
=INT((DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"))/12) &" Years " & MOD(DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"),12) &" months" If this post helps click Yes --------------- Jacob Skaria "PJS" wrote: Question Let's take for example a Hire-date of 10/16/06 in cell A2 =DATEDIF(A2,NOW(),"y")&" years "&DATEDIF(A2,NOW(),"ym")&" months " and I get 2 years and 10 months but now I have 3 dates... Hire-date, Term-date, Rehire-date Is there a forumula that would calculate the years and months in between? For example Hire-date = 10/23/06 Term-date = 08/02/08 Rehire-date = 04/06/09 I was able to calculate the months using the forumula =(DATEDIF(B3,C3,"M")+DATEDIF(D3,NOW(),"M")) = 25 but I am having some trouble changing the 25 into 2 years 1 month Thanks, PJS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating time between mulitple dates
Thanks Jacob Skaria, the forumla works...
but I neglected to mentioned that C3 and D3 (Term-date and Rehire-date) can be null.... thus resulting in a NUM error... "Jacob Skaria" wrote: Try =INT((DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"))/12) &" Years " & MOD(DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"),12) &" months" If this post helps click Yes --------------- Jacob Skaria "PJS" wrote: Question Let's take for example a Hire-date of 10/16/06 in cell A2 =DATEDIF(A2,NOW(),"y")&" years "&DATEDIF(A2,NOW(),"ym")&" months " and I get 2 years and 10 months but now I have 3 dates... Hire-date, Term-date, Rehire-date Is there a forumula that would calculate the years and months in between? For example Hire-date = 10/23/06 Term-date = 08/02/08 Rehire-date = 04/06/09 I was able to calculate the months using the forumula =(DATEDIF(B3,C3,"M")+DATEDIF(D3,NOW(),"M")) = 25 but I am having some trouble changing the 25 into 2 years 1 month Thanks, PJS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating time between mulitple dates
Try the below
=INT((DATEDIF(B3,IF(C3,C3,TODAY()),"m")+IF(D3,DATE DIF(D3,TODAY(),"m"),0))/12) &" Years " & MOD(DATEDIF(B3,IF(C3,C3,TODAY()),"m")+IF(D3,DATEDI F(D3,TODAY(),"m"),0),12) &" months" If this post helps click Yes --------------- Jacob Skaria "PJS" wrote: Thanks Jacob Skaria, the forumla works... but I neglected to mentioned that C3 and D3 (Term-date and Rehire-date) can be null.... thus resulting in a NUM error... "Jacob Skaria" wrote: Try =INT((DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"))/12) &" Years " & MOD(DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"),12) &" months" If this post helps click Yes --------------- Jacob Skaria "PJS" wrote: Question Let's take for example a Hire-date of 10/16/06 in cell A2 =DATEDIF(A2,NOW(),"y")&" years "&DATEDIF(A2,NOW(),"ym")&" months " and I get 2 years and 10 months but now I have 3 dates... Hire-date, Term-date, Rehire-date Is there a forumula that would calculate the years and months in between? For example Hire-date = 10/23/06 Term-date = 08/02/08 Rehire-date = 04/06/09 I was able to calculate the months using the forumula =(DATEDIF(B3,C3,"M")+DATEDIF(D3,NOW(),"M")) = 25 but I am having some trouble changing the 25 into 2 years 1 month Thanks, PJS |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating time between mulitple dates
Thanks Jacob, it is working!
"Jacob Skaria" wrote: Try the below =INT((DATEDIF(B3,IF(C3,C3,TODAY()),"m")+IF(D3,DATE DIF(D3,TODAY(),"m"),0))/12) &" Years " & MOD(DATEDIF(B3,IF(C3,C3,TODAY()),"m")+IF(D3,DATEDI F(D3,TODAY(),"m"),0),12) &" months" If this post helps click Yes --------------- Jacob Skaria "PJS" wrote: Thanks Jacob Skaria, the forumla works... but I neglected to mentioned that C3 and D3 (Term-date and Rehire-date) can be null.... thus resulting in a NUM error... "Jacob Skaria" wrote: Try =INT((DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"))/12) &" Years " & MOD(DATEDIF(B3,C3,"m")+DATEDIF(D3,TODAY(),"m"),12) &" months" If this post helps click Yes --------------- Jacob Skaria "PJS" wrote: Question Let's take for example a Hire-date of 10/16/06 in cell A2 =DATEDIF(A2,NOW(),"y")&" years "&DATEDIF(A2,NOW(),"ym")&" months " and I get 2 years and 10 months but now I have 3 dates... Hire-date, Term-date, Rehire-date Is there a forumula that would calculate the years and months in between? For example Hire-date = 10/23/06 Term-date = 08/02/08 Rehire-date = 04/06/09 I was able to calculate the months using the forumula =(DATEDIF(B3,C3,"M")+DATEDIF(D3,NOW(),"M")) = 25 but I am having some trouble changing the 25 into 2 years 1 month Thanks, PJS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating time between two dates | Excel Discussion (Misc queries) | |||
| Calculating time between two dates | Excel Discussion (Misc queries) | |||
| Calculating time between two dates | Excel Discussion (Misc queries) | |||
| Calculating time differences across two (or more) dates | Excel Discussion (Misc queries) | |||
| calculating time/dates differences | Excel Discussion (Misc queries) | |||
Linear Mode
