Find the difference in days, hours and minutes between two dates and times

[Antoan]

Hello!



I have cyrrently faced the below stated obstacles. In order to check the actual delay in the sheet below accurately, I decided to consolidate both dates and times into one sheet with the formula: =CONCATENATE(TEXT(M10;"mm/dd/yyyy")&" ; "&TEXT(N10;"hh:mm:ss")). Now I would like to find the difference between the earliest start date and the actual start time in days, hours, minutes, however due to the formating i suppose the simple formula =A1-B1 wouldnt work. Moreover, i tried the one that you can see in the sheet below and I also tried this one:=INT(C5-B5)&" days "&TEXT(C5-B5,"h"" hrs ""m"" mins """) but still turned out to be impossible... Could you maybe give any suggestions or point out at something that i have missed in the sheet below because honestly I dont know why it keeps not happening!

Thank you in advance!

alle-linjer-samlet-.xlsx
	G			J					O	P			S			V	W	X
1	Earliest start			Earliest finish2					SAME DAY	Latest Start2			Latest finish			Actual finish	Actual Start2	Comparison Earliest/ActualSTART
2	09/18/2017 ; 07:00:00			09/18/2017 ; 08:00:00					TRUE	09/18/2017 ; 07:00:00			09/18/2017 ; 08:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
3	09/23/2017 ; 07:00:00			09/23/2017 ; 08:00:00					TRUE	09/23/2017 ; 07:00:00			09/23/2017 ; 08:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
4	09/23/2017 ; 08:00:00			09/23/2017 ; 11:00:00					TRUE	09/23/2017 ; 08:00:00			09/23/2017 ; 11:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
5	09/25/2017 ; 08:00:00			09/25/2017 ; 15:00:00					TRUE	08/03/2012 ; 14:12:00			08/06/2012 ; 12:12:00			09/25/2017 ; 08:18:33	09/23/2017 07:48:14	#VALUE!
6	09/23/2017 ; 11:30:00			09/23/2017 ; 13:30:00					TRUE	09/23/2017 ; 11:30:00			09/23/2017 ; 13:30:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
7	09/23/2017 ; 13:30:00			09/23/2017 ; 14:30:00					TRUE	09/23/2017 ; 13:30:00			09/23/2017 ; 14:30:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
8	08/01/2017 ; 07:00:00			08/01/2017 ; 15:00:00					TRUE	08/01/2017 ; 07:00:00			08/01/2017 ; 15:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
9	10/09/2017 ; 07:00:00			10/09/2017 ; 07:00:00					TRUE	10/09/2017 ; 07:00:00			10/09/2017 ; 07:00:00			09/23/2017 ; 14:00:00	09/23/2017 07:00:00	#VALUE!
10	10/09/2017 ; 07:00:00			10/09/2017 ; 12:30:00					TRUE	10/09/2017 ; 07:00:00			10/09/2017 ; 12:30:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
11	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00					TRUE	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00			01/20/2012 ; 14:25:15	09/29/2011 07:00:00	#VALUE!
12	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00					TRUE	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00			01/26/2012 ; 11:58:15	01/26/2012 08:00:00	#VALUE!
13	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00					TRUE	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00			02/28/2012 ; 07:25:54	01/02/2012 14:30:00	#VALUE!
14	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00					TRUE	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00			09/18/2013 ; 10:03:08	01/02/2013 08:00:00	#VALUE!
15	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00					TRUE	07/07/2017 ; 00:00:00			07/07/2017 ; 00:00:00			01/26/2012 ; 11:58:15	01/09/2012 07:00:00	#VALUE!
Sheet5

Cell Formulas
Range	Formula
O2:O15	O2	=IF(G2=P2,FALSE,TRUE)
P2:P15	P2	=CONCATENATE(TEXT(M2,"mm/dd/yyyy")&" ; "&TEXT(N2,"hh:mm:ss"))
V2:V15,S2:S15,J2:J15	V2	=CONCATENATE(TEXT(T2,"mm/dd/yyyy")&" ; "&TEXT(U2,"hh:mm:ss"))
W2:W15	W2	=CONCATENATE(TEXT(K2,"mm/dd/yyyy")&" "&TEXT(L2,"hh:mm:ss"))
X2:X15	X2	=TEXT([@[Earliest start]]-[@[Actual Start2]],"dd:hh:mm:ss")
G2:G15	G2	=CONCATENATE(TEXT(E2,"mm/dd/yyyy")&" ; "&TEXT(F2,"hh:mm:ss"))

 

[Gerald Higgins]

If M10 contains a date, and N10 contains a time, then you can use

=M10+N10

to combine the two into a date / time combination.

You can then subtract some other date and time from the result.

 

[Dave Patton]

Review the example below and then edit your spreadsheet

T202205a.xlsm
	B	C	D	E	F	G	H
1	Date 1	Time 1	Date 2	Time 2	Time Diff		Time difference
2	18-Sep-17	07:00	18-Sep-17	08:00	01:00:00
3	18-Sep-17	07:00	18-Sep-17	20:00	13:00:00
4	18-Sep-17	07:00	20-Sep-17	20:00	61:00:00	custom format [h]:mm:ss
5	18-Sep-17	07:00	20-Sep-17	20:00	2:13:00:00	custom format d:hh:mm:ss
6
1a

Cell Formulas
Range	Formula
F2:F5	F2	=(D2+E2)-(B2+C2)

 

[Antoan]

If M10 contains a date, and N10 contains a time, then you can use

=M10+N10

to combine the two into a date / time combination.

You can then subtract some other date and time from the result.

Hej!


That's almost exactly what i have in the comparison column: =TEXT([@[Earliest start]]-[@[Actual Start2]],"dd:hh:mm:ss"), tried you version as well but it just says #VALUE! as shown below:

alle-linjer-samlet-.xlsx
	S			V	W	X
1	Latest finish			Actual finish	Actual Start4	Comparison Earliest/ActualSTART
2	09/18/2017 ; 08:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
3	09/23/2017 ; 08:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
4	09/23/2017 ; 11:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
5	08/06/2012 ; 12:12:00			09/25/2017 ; 08:18:33	09/23/2017 07:48:14	#VALUE!
6	09/23/2017 ; 13:30:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
7	09/23/2017 ; 14:30:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
8	08/01/2017 ; 15:00:00			01/00/1900 ; 00:00:00	01/00/1900 00:00:00	#VALUE!
9	10/09/2017 ; 07:00:00			09/23/2017 ; 14:00:00	09/23/2017 07:00:00	#VALUE!
data

Cell Formulas
Range	Formula
V2:V9,S2:S9	V2	=CONCATENATE(TEXT(T2,"mm/dd/yyyy")&" ; "&TEXT(U2,"hh:mm:ss"))
W2:W9	W2	=CONCATENATE(TEXT(K2,"mm/dd/yyyy")&" "&TEXT(L2,"hh:mm:ss"))
X2:X9	X2	=master[@[Earliest start]]-[@[Actual Start4]]

 

[Antoan]

Review the example below and then edit your spreadsheet

T202205a.xlsm
	B	C	D	E	F	G	H
1	Date 1	Time 1	Date 2	Time 2	Time Diff		Time difference
2	18-Sep-17	07:00	18-Sep-17	08:00	01:00:00
3	18-Sep-17	07:00	18-Sep-17	20:00	13:00:00
4	18-Sep-17	07:00	20-Sep-17	20:00	61:00:00	custom format [h]:mm:ss
5	18-Sep-17	07:00	20-Sep-17	20:00	2:13:00:00	custom format d:hh:mm:ss
6
1a

Cell Formulas
Range	Formula
F2:F5	F2	=(D2+E2)-(B2+C2)

That worked perfectly!

Thank you so much for your help!

 

[Dave Patton]

thanks for the feedback. Dave