Duration in Years - Month - Days - Hours - Minutes

[shah_ir85]

Hey mate....please help me

i have to calculate duration for 2 date and time. Please help me with the formula on C.

For example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Start
[/TD]
[TD]End
[/TD]
[TD]Duration
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1 Jan 16 13:30
[/TD]
[TD]3 Apr 17 23:45
[/TD]
[TD]1 Year 3 Month 2 Days 10 Hours 15 Minutes
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1 Apr 15 12:40
[/TD]
[TD]1 May 17 13:45
[/TD]
[TD]2 Years 1 Month 1 Hour 5 Minutes
[/TD]
[/TR]
</tbody>[/TABLE]

Please help me if you can tQ

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C	D	E
1
2	Start	End	Duration
3	1-1-2016 13:30	3-4-2017 23:45	1 Year 3 Month 2 Days 10 Hours 15 Minutes		1 Year 3 Months 2 Days 10 Hours 15 Minutes
4	1-4-2015 12:40	1-5-2017 13:45	2 Years 1 Month 1 Hour 5 Min		2 Years 1 Month 1 Hour 5 Minutes
Sheet2

Cell Formulas
Range	Formula
E3	=IF(DATEDIF(A3,B3,"y")=0,"",DATEDIF(A3,B3,"y")&LOOKUP(DATEDIF(A3,B3,"y"),{0,2},{" Year "," Years "}))&IF(DATEDIF(A3,B3,"ym")=0,"", DATEDIF(A3,B3,"ym")&LOOKUP(DATEDIF(A3,B3,"ym"),{0,2},{" Month "," Months "}))&IF(DATEDIF(A3,B3,"md")=0,"", DATEDIF(A3,B3,"md")&LOOKUP(DATEDIF(A3,B3,"md"),{0,2},{" Day "," Days "}))&IF(HOUR(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))=0,"",HOUR(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))&LOOKUP(HOUR(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1)),{0,2},{" Hour "," Hours "}))&IF(MINUTE(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))=0,"",MINUTE(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))&LOOKUP(MINUTE(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1)),{0,2},{" Minute "," Minutes "}))

 

[shah_ir85]

Yes....tQvm for help

 

[shah_ir85]

hey mate...sorry again...error occur for example

start date 6 Aug 17 2230
end date 8 Aug 17 1445

duration 2 Days 16 Hours 15 Minutes

Its should be 1 Day 16 Hours 15 Minutes

please help me

 

[Dave Patton]

custom format d:hh:mm:ss;@

Excel 2010
	A	B	C
1	6-Aug-17	8-Aug-17	1:16:15:00
5f

Cell Formulas
Range	Formula
C1	=B1-A1

 

[jorismoerings]

try this:

Book1
	A	B	C	D	E
2	Start	End	Duration
3	1-1-2016 13:30	3-4-2017 23:45	1 Year 3 Month 2 Days 10 Hours 15 Minutes		1 Year 3 Months 2 Days 10 Hours 15 Minutes
4	1-4-2015 12:40	1-5-2017 13:45	2 Years 1 Month 1 Hour 5 Min		2 Years 1 Month 1 Hour 5 Minutes
5	6-8-2017 22:30	8-8-2017 14:45			1 Day 16 Hours 15 Minutes
6	6-8-2017 22:30	8-8-2017 22:31			2 Days 1 Minute
Sheet1

Cell Formulas
Range	Formula
E3	=IF(DATEDIF(A3,B3,"y")=0,"",DATEDIF(A3,B3,"y")&LOOKUP(DATEDIF(A3,B3,"y"),{0,2},{" Year "," Years "}))&IF(DATEDIF(A3,B3,"ym")=0,"", DATEDIF(A3,B3,"ym")&LOOKUP(DATEDIF(A3,B3,"ym"),{0,2},{" Month "," Months "}))&IF(DATEDIF(A3,B3,"md")=0,"", DATEDIF(A3,B3,"md")-IF((MOD(B3,1)A3,1)),1,0)&LOOKUP(DATEDIF(A3,B3,"md")-IF((MOD(B3,1)A3,1)),1,0),{0,2},{" Day "," Days "}))&IF(HOUR(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))=0,"",HOUR(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))&LOOKUP(HOUR(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1)),{0,2},{" Hour "," Hours "}))&IF(MINUTE(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))=0,"",MINUTE(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1))&LOOKUP(MINUTE(MOD(B3,1)+(MOD(A3,1)>MOD(B3,1))-MOD(A3,1)),{0,2},{" Minute "," Minutes "}))

 

[shah_ir85]

tQvm mate its work

 

[Chhabi]

@jorismoerings,
That is a great job! Now, could you add little bit more so as to include seconds, too? Thanks!