Calculating total years and months in between 2 dates, representing the months in decimal

[mart_mrexcel]

I have 2 dates 10-June-19 and today's Date (eg19-Dec-20), i used the formula as: =(DATEDIF([@[Joining Date]],TODAY(),"Y")&"."&DATEDIF([@[Joining Date]],TODAY(),"ym")). My Query is:

1.) How can i have a result as 1.06 (i.e. .06 as the months), instead of 1.6
2.) If in case the decimal reaches the .12, it will add to the whole number so it will be 2
3.) if i have a total years of 1.06 and i wanted to add in 3.10, how can i have a result of 5.04 (i.e. 5yrs and 4 months)


Thanks

 

[maabadi]

This is formula:

=(DATEDIF($A$12:$A$30,TODAY(),"Y")&"."&IF(DATEDIF($A$12:$A$30,TODAY(),"ym")<10,0&DATEDIF($A$12:$A$30,TODAY(),"ym"),DATEDIF($A$12:$A$30,TODAY(),"ym")))

2. It doesn't reach .12, if the months was same it return 0 not 12,
3. for adding don't have problem, it added.

 

[mart_mrexcel]

This is formula:

=(DATEDIF($A$12:$A$30,TODAY(),"Y")&"."&IF(DATEDIF($A$12:$A$30,TODAY(),"ym")<10,0&DATEDIF($A$12:$A$30,TODAY(),"ym"),DATEDIF($A$12:$A$30,TODAY(),"ym")))

2. It doesn't reach .12, if the months was same it return 0 not 12,
3. for adding don't have problem, it added.

Thanks. for item no. 3, actual it does not come in between 2 dates, it is a whole figure will be added in the item no. 1

 

[maabadi]

For Item 3 use this formula:
If your Previous formula is in F2 and adding value in G2

=IF(MOD(F2+G2,1)>=0.12,F2+G2+1-0.12,F2+G2)

 

[mart_mrexcel]

ADVERTISEMENT

For Item 3 use this formula:
If your Previous formula is in F2 and adding value in G2

=IF(MOD(F2+G2,1)>=0.12,F2+G2+1-0.12,F2+G2)

sir, I put 06-June-2020 giving the correct result as 0.06, if added it to 7.06 experience it gives me 7.12, how can i reflect it as 8

 

[maabadi]

Change formula to this:

=IF(MOD(F2+G2,1)>0.11,F2+G2+1-0.12,F2+G2)

 

[mart_mrexcel]

Change formula to this:

=IF(MOD(F2+G2,1)>0.11,F2+G2+1-0.12,F2+G2)

Thanks a lot. that works perfect. have a nice day

 

[maabadi]

You 're Welcome & thanks for feedback.