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]

Did you review or try any of the formulas?
Which calculation are you referring to?
The hours calculations reconcile.

=(INT(B2)-INT(A2)-1)*24+F2*24 = complete days *24 + 1 minute start date + 16 hours and 41 minutes end date

 

[ljubo_gr]

I have to give up the DATEDIF function. Gotta use Year Eomonth etc, to manually calculate difference.
I have to calculate hhmmss and substract to days and days substract to months, to have: 0y 10m 29,7d 17:29 i guess.

 

[Dave Patton]

Good luck on your project.

The following shows the calculation of total hours.

Datedif.xlsm
	A	B	C	D	E
1	Start	End	Days Inclusive	Hours
2	01-Jan-2021 00:00:01	31-Dec-2021 23:59:59	365	8,759.999
3	01-Jan-2020 06:00:00	31-Dec-2020 18:00:00	366	8,772.000	leap year
4	01-Jan-2021	31-Dec-2021	365	8,760.000
5
1aa

Cell Formulas
Range	Formula
C2:C3	C2	=INT(B2)-INT(A2)+1
D2:D3	D2	=(C2-2)*24+(1-MOD(A2,1))*24+(MOD(B2,1))*24+N("Total day -2 to exclude start and end days+Start hours +End hours")
C4	C4	=B4-A4+1
D4	D4	=365*24

 

[ljubo_gr]

Thank you Dave, i'll give up, cannot calculate exact days, moths, years elapsed.

 

[Dave Patton]

Row 2 is inclusive; the hours in the start date and the hours in the end date are included.
If a person works Monday to Friday, is that 4 days or 5 days? 5-1 =4 5-1+1= 5

Datedif.xlsm
	A	B	C	D	E	F	G
7	Mon 01-Jan-18	Fri 05-Jan-18	0	0	5	5.0	120
1a

Cell Formulas
Range	Formula
C7	C7	=DATEDIF(A7-1,B7,"y")
D7	D7	=DATEDIF(A7-1,B7,"ym")
E7	E7	=DATEDIF(A7-1,B7,"MD")
F7	F7	=B7-A7+1
G7	G7	=F7*24

A non leap year is 365 days; a year that includes Feb 29 is 366. Should the calculation reflect 366 days?

Datedif.xlsm
	A	B	C	D	E	F	G
1	Start	End	Years	Months	Days	T Days	HH:MM:SS
2	15-Apr-2020 23:59	15-Mar-2021 16:41	0	11	1	334.7	8008:42
3	15-Apr-2020	15-Mar-2021	0	11	1	335.0	8040
4	01-Jan-2020	30-Apr-2021	1	3	30	486.0	11664
5	01-Jan-2020	31-Dec-2020	1	0	0	366.0	8784
1a

Cell Formulas
Range	Formula
C2:C5	C2	=DATEDIF(A2-1,B2,"y")
D2:D5	D2	=DATEDIF(A2-1,B2,"ym")
E2:E5	E2	=DATEDIF(A2-1,B2,"MD")
F2:F5	F2	=B2-A2+1
G2	G2	=(INT(B2)-INT(A2)-1)+1-MOD(A2,1)+MOD(B2,1)
G3:G5	G3	=F3*24

 

[ljubo_gr]