Difference between dates in Years, Months...

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Hi,
Please, help. Excel is still mystery to me.

I worked before with =DATEDIF() but I want avoid "MD" unit as it's prone to inaccurate result.
I need difference between dates as: in Years, Months, Days, hh:mm:ss, four columns and additional columns as simple =end-startDate as elapsed Days and one column as total elapsed [HH]:mm:ss.
I will only have start date, because i will use =NOW() or =Today() for end date. But, formula should work with cell values also.(Start Date(smaller), EndDate(bigger date))

I have problems with third column( DAYS). I have wrong results with formula: =DAYS(TODAY();EDATE(E4;J4)) where E4 is startDate and J4 is value from =datedif(small, big, "YM") for months.

I NEED e.g.: 15/04/2020 23:59 =NOW() 15/03/2021 16:41 0y 10m 30d 7:18 330d [1569]:26:59 <<< approximately

Thank you in advance, God Bless!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How did you calculate your example?
What dates do you have trouble with.
What version of Excel are you using? Please edit your profile.
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
0y 10m 30d 7:18 330d [1569]:26:59 <<< approximately
By heart.

Trouble with... like everything!
Where is 30d, datedif is giving 388d.
Office2016.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can try the following.
Test with your dates and advise.
Post an extract of your spreadsheet with the forum's XL2BB.

Datedif.xlsm
ABCDE
1StartEndYearsMonthsDays
215-04-2020 23:5915-03-2021 16:410110
315-Apr-2015-Mar-210110
41-Jan-2031-Dec-20100
51-Jan-2030-04-21140
61-Jan-2031-Dec-20100
1a
Cell Formulas
RangeFormula
C2:C6C2=DATEDIF(A2,B2+1,"y")
D2:D6D2=DATEDIF(A2,B2+1,"ym")
E2:E6E2=DATEDIF(A2,B2+1,"MD")-(OR(MONTH(A2)={4,6,9,11}))
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Dave, Thanks for the quick reply
Formulae for Y, M, and Days are working correctly. Now i need in F column reminder of days in hh:mm:ss
StartEndYearsMonthdDaysHH:MM:SSDAYZtotal hrs.
15-04-2020 23:59
15-03-2021 16:41=DATEDIF(A2,B2,"Y")=DATEDIF(A2,B2,"YM")=DATEDIF(EDATE(EDATE(A2,C2*12),D2),B2,"D")?????=B2-A2=B2-A2 form.as [HH]:mm:ss
 
Last edited:

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Snimka zaslona 2021-03-16 002933.png
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

N.B.
You did not:
provide a variety of dates, sample data, or expected results.
a complete explanation what what you are calculating and the challenges

Datedif does not show accurate days with certain Start and End dates

Please show how would you manually calculate the hh:mm:ss.

I show a guess below. 333 days at 24 hours plus 1 minute + 16 hours and 41 minutes

Datedif.xlsm
ABCDEFGH
1StartEndYearsMonthsDaysHH:MM:SS
215-04-2020 23:5915-03-2021 16:41011016:42:008,008.708,008.70
1a
Cell Formulas
RangeFormula
C2C2=DATEDIF(A2,B2,"y")
D2D2=DATEDIF(A2,B2,"ym")
E2E2=DATEDIF(A2,B2,"MD")
F2F2=1-MOD(A2,1)+MOD(B2,1)
G2G2=(B2-A2)*24
H2H2=(INT(B2)-INT(A2)-1)*24+F2*24
 
Last edited:

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Hi, Dave,
I'm Sorry, i don't understood why you where using 1 in DATEDIF(A2,B2+1.
In my example date, there is wrong 11 months, bcause 16:41hrs is less than 23:59.
So, eider DATEDIF cannot work with TIME or I have to manually calculate formula with 86400, 1440.....etc.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Please read or try post #7.
Click on the icon below F(x) and paste into a clean sheet.
Datedif ignores the time.
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Please read or try post #7.
Click on the icon below F(x) and paste into a clean sheet.
Datedif ignores the time.
I+m lost, Dave, please help. WHAT'S THE DIFFERENCE BETWEEN THOSE DATES IN: YEARS,MONTHS,DAYS,HH:MMSS???
15-4-20 23:59 15-03-20 16:41 is 0y 10m 30d 17:29 i guess.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,358
Messages
5,635,800
Members
416,883
Latest member
jwchase

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