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

mart_mrexcel

Board Regular
Joined
Aug 23, 2008
Messages
231
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is formula:
Excel 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

Board Regular
Joined
Aug 23, 2008
Messages
231
This is formula:
Excel 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

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
For Item 3 use this formula:
If your Previous formula is in F2 and adding value in G2
Excel Formula:
=IF(MOD(F2+G2,1)>=0.12,F2+G2+1-0.12,F2+G2)
 

mart_mrexcel

Board Regular
Joined
Aug 23, 2008
Messages
231

ADVERTISEMENT

For Item 3 use this formula:
If your Previous formula is in F2 and adding value in G2
Excel Formula:
=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

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change formula to this:
Excel Formula:
=IF(MOD(F2+G2,1)>0.11,F2+G2+1-0.12,F2+G2)
 
Solution

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,343
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You 're Welcome & thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,120
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top