Difference between dates in Years, Months...

[ljubo_gr]

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!

 

[Dave Patton]

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]

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]

You can try the following.
Test with your dates and advise.
Post an extract of your spreadsheet with the forum's XL2BB.

Datedif.xlsm
	A	B	C	D	E
1	Start	End	Years	Months	Days
2	15-04-2020 23:59	15-03-2021 16:41	0	11	0
3	15-Apr-20	15-Mar-21	0	11	0
4	1-Jan-20	31-Dec-20	1	0	0
5	1-Jan-20	30-04-21	1	4	0
6	1-Jan-20	31-Dec-20	1	0	0
1a

Cell Formulas
Range	Formula
C2:C6	C2	=DATEDIF(A2,B2+1,"y")
D2:D6	D2	=DATEDIF(A2,B2+1,"ym")
E2:E6	E2	=DATEDIF(A2,B2+1,"MD")-(OR(MONTH(A2)={4,6,9,11}))

 

[ljubo_gr]

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

Start	End	Years	Monthd	Days	HH:MM:SS	DAYZ	total 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

 

[ljubo_gr]

 

[Dave Patton]

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
	A	B	C	D	E	F	G	H
1	Start	End	Years	Months	Days	HH:MM:SS
2	15-04-2020 23:59	15-03-2021 16:41	0	11	0	16:42:00	8,008.70	8,008.70
1a

Cell Formulas
Range	Formula
C2	C2	=DATEDIF(A2,B2,"y")
D2	D2	=DATEDIF(A2,B2,"ym")
E2	E2	=DATEDIF(A2,B2,"MD")
F2	F2	=1-MOD(A2,1)+MOD(B2,1)
G2	G2	=(B2-A2)*24
H2	H2	=(INT(B2)-INT(A2)-1)*24+F2*24

 

[ljubo_gr]

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]

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]

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.