Difference between two cells data in Years, Months, Days, Hours, Minutes

[tree5ppm]

Hello fellows,
Thank you for great forum that helps most of the formula doubts.

in my case. A2 has 2019-03-10 14:30
A3 has 2021-11-21 05:00

I would like to get results in B4 like "986 days, 14 hours, 30 minutes"
in B5 "2 years, 8 months, 10 days, 14 hours, 30 minutes"
in B6 "32 months, 10 days, 14 hours, 30 minutes"

Kind Regards

 

[etaf]

I have used datedif , and get 11 days , maybe because of the way datedif works and counts complete days
B4
=INT(A3-A2) &" Days " & HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B5
=DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B6
=DATEDIF(A2,A3,"M") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "

I'll need to look into and see what the day is in more detail and if just using a -1 would work - because its including the 1st day

Book2
	A	B
1
2	3/10/19 14:30
3	11/21/21 5:00
4		986 Days 14 Hours 30 Minutes
5		2 Years 8 Months 11 Days 14 Hours 30 Minutes
6		32 Months 11 Days 14 Hours 30 Minutes
7
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INT(A3-A2) &" Days " & HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B5	B5	=DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B6	B6	=DATEDIF(A2,A3,"M") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "

Yes its counting the Days ignoring the hours
for example, using the -1 otherwise we get 1 day

Book2
	A	B
2	3/10/19 14:30
3	3/11/19 5:00
4		0 Days 14 Hours 30 Minutes
5		0 Years 0 Months 0 Days 14 Hours 30 Minutes
6		0 Months 0 Days 14 Hours 30 Minutes
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INT(A3-A2) &" Days " & HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B5	B5	=DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-1 & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B6	B6	=DATEDIF(A2,A3,"M") & " Months " & DATEDIF(A2,A3,"MD") -1& " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "

 

[jasonb75]

I'll need to look into and see what the day is in more detail and if just using a -1 would work - because its including the 1st day

DATEDIF will not see the time portion of what is in the cells, you need to subtract 1 when the end time is earlier than the start time.

 

[etaf]

then we need to test the TIME to see if less or greater
so for the 2nd formula - will this work for you
=IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIME(HOUR(A3),MINUTE(A3),SECOND(A3)),DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes ",DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-1 & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes ")

=IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIME(HOUR(A3),MINUTE(A3),SECOND(A3)) we are testing the time to see if greater or less than , so has a day elapsed ?
If A2 is less then or equals A3 in time - we dont need to subtract a day
,DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes ",
if A2 is greater than A3
DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-1 & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes ")

 

[jasonb75]

Could do it a bit shorter. Borrowing your original 2nd formula as a starting block

=DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-(MOD(B2,1)<MOD(A2,1)) & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "

 

[etaf]

thanks for simplifying formula for OP - but i get a zero days when only 1 day apart

Book2
	A	B	C	D	E
2	3/10/19 15:30
3	3/11/19 15:30
4		1 Days 0 Hours 0 Minutes
5		0 Years 0 Months 1 Days 0 Hours 0 Minutes			0 Years 0 Months 0 Days 0 Hours 0 Minutes
6		0 Months 0 Days 0 Hours 0 Minutes
Sheet1

Cell Formulas
Range	Formula
E5	E5	=DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-(MOD(B2,1)<MOD(A2,1)) & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B4	B4	=INT(A3-A2) &" Days " & HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "
B5	B5	=IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIME(HOUR(A3),MINUTE(A3),SECOND(A3)),DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD") & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes ",DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-1 & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes ")
B6	B6	=DATEDIF(A2,A3,"M") & " Months " & DATEDIF(A2,A3,"MD") -1& " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "

 

[jasonb75]

Sorry, typo. I entered a wrong cell reference.

=DATEDIF(A2,A3,"Y") & " Years " & DATEDIF(A2,A3,"YM") & " Months " & DATEDIF(A2,A3,"MD")-(MOD(A3,1)<MOD(A2,1)) & " Days "& HOUR((A3-A2) ) & " Hours " & MINUTE((A3-A2) ) & " Minutes "