Date and Time difference not calculating over 30 days

[Stef9910]

Hello,

Please could some one help me with where I am going wrong with a formula.

The formula I am having trouble with is =IFERROR(IF(K26="", "", IF(M26="", IF(N26=K26, "", N26-K26), IF(N26<>"", N26, M26)-K26)),"")

This formula needs to display days hours and minutes, i have the cell formatted under custom as d "days" h "h" mm "m"

As you can see from the below table, Cell P26 is displaying 30 days 2 h 04 m, what it be displaying is 182 days 2 h 04 m

If can the custom format to to include months, it displays correctly, but incorrectly displays the results in the other cells in column P

I would like it to just display the days hours and minutes.

Any help will be greatly appreciated,

Thanks Stefan

formulas HS sheet.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
25	Received Time	Start Date	Start Time	End Date	End Time	RTP	RTP Time	Received Date	Start Date & Time	End Date & Time	RTP Date & Time	Days, Hours, Minutes until Started	Total Span	Facilitated Span
26	19:16	31/12/22	19:45	1/7/23	21:20			31/12/2022 19:16	31/12/2022 19:45	1/07/2023 21:20		0 days 0 h 29 m	30 days 2 h 04 m	30 days 1 h 35 m
27	11:26					2/6/23	12:58	22/05/2023 11:26			2/06/2023 12:58		11 days 1 h 32 m
28	6:30	25/5/23	15:15	7/6/23	14:30			23/05/2023 6:30	25/05/2023 15:15	7/06/2023 14:30		2 days 8 h 45 m	15 days 8 h 00 m	12 days 23 h 15 m
29	0:46	19/5/23	20:14	13/6/23	20:00			18/05/2023 0:46	19/05/2023 20:14	13/06/2023 20:00		1 days 19 h 28 m	26 days 19 h 14 m	24 days 23 h 46 m
30	21:25	25/5/23	19:40					24/05/2023 21:25	25/05/2023 19:40			0 days 22 h 15 m
Sheet1

Cell Formulas
Range	Formula
K26:K30	K26	=C26+D26
L26:L30	L26	=IFERROR(IF(E26+F26=DATE(1900,1,0),"",E26+F26),"")
M26:M30	M26	=IFERROR(IF(G26+H26=DATE(1900,1,0),"",G26+H26),"")
N26:N30	N26	=IFERROR(IF(I26+J26=DATE(1900,1,0),"",I26+J26),"")
O26:O30	O26	=IFERROR(IF(L26-K26=DATE(1900,1,0),"",L26-K26),"")
P26:P30	P26	=IFERROR(IF(K26="", "", IF(M26="", IF(N26=K26, "", N26-K26), IF(N26<>"", N26, M26)-K26)),"")
Q26:Q30	Q26	=IFERROR(IF(M6-L26=DATE(1900,1,0),"",M26-L26),"")

 

[awoohaw]

you have not copied what is in column C.

I think you must have inserted a column or changed a formula that messed up your cell references. It seems you columns are all off by 1.

Please look at that, and if you still have a problem repost a new xl2bb mini sheet as the one in post 1 seems corrupted.

 

[Stef9910]

you have not copied what is in column C.

I think you must have inserted a column or changed a formula that messed up your cell references. It seems you columns are all off by 1.

Please look at that, and if you still have a problem repost a new xl2bb mini sheet as the one in post 1 seems corrupted.

Thanks for your reply awoohaw,
My Mistake, I didn't include column C,
I have rechecked the formula's and still cannot work out where I am going wrong,

Below is the minisheet including column C,

formulas HS sheet.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
25	Received Date	Received Time	Start Date	Start Time	End Date	End Time	RTP	RTP Time	Received Date	Start Date & Time	End Date & Time	RTP Date & Time	Days, Hours, Minutes until Started	Total Span	Facilitated Span
26	31/12/22	19:16	31/12/22	19:45	1/7/23	21:20			31/12/2022 19:16	31/12/2022 19:45	1/07/2023 21:20		0 days 0 h 29 m	30 days 2 h 04 m	30 days 1 h 35 m
27	22/5/23	11:26					2/6/23	12:58	22/05/2023 11:26			2/06/2023 12:58		11 days 1 h 32 m
28	23/5/23	6:30	25/5/23	15:15	7/6/23	14:30			23/05/2023 6:30	25/05/2023 15:15	7/06/2023 14:30		2 days 8 h 45 m	15 days 8 h 00 m	12 days 23 h 15 m
29	18/5/23	0:46	19/5/23	20:14	13/6/23	20:00			18/05/2023 0:46	19/05/2023 20:14	13/06/2023 20:00		1 days 19 h 28 m	26 days 19 h 14 m	24 days 23 h 46 m
30	24/5/23	21:25	25/5/23	19:40					24/05/2023 21:25	25/05/2023 19:40			0 days 22 h 15 m
Sheet1

Cell Formulas
Range	Formula
K26:K30	K26	=C26+D26
L26:L30	L26	=IFERROR(IF(E26+F26=DATE(1900,1,0),"",E26+F26),"")
M26:M30	M26	=IFERROR(IF(G26+H26=DATE(1900,1,0),"",G26+H26),"")
N26:N30	N26	=IFERROR(IF(I26+J26=DATE(1900,1,0),"",I26+J26),"")
O26:O30	O26	=IFERROR(IF(L26-K26=DATE(1900,1,0),"",L26-K26),"")
P26:P30	P26	=IFERROR(IF(K26="", "", IF(M26="", IF(N26=K26, "", N26-K26), IF(N26<>"", N26, M26)-K26)),"")
Q26:Q30	Q26	=IFERROR(IF(M6-L26=DATE(1900,1,0),"",M26-L26),"")

 

[Stef9910]

you have not copied what is in column C.

I think you must have inserted a column or changed a formula that messed up your cell references. It seems you columns are all off by 1.

Please look at that, and if you still have a problem repost a new xl2bb mini sheet as the one in post 1 seems corrupted.

Hi awoohaw,

I have redone the formula in a different way, which has worked, I used =IFERROR(IF(K26="", "", INT(IF(M26="", N26, M26)-K26) & " Days " & HOUR(IF(M26="", N26, M26)-K26) & " h " & MINUTE(IF(M26="", N26, M26)-K26) & " m"), "")

 

[awoohaw]

Hi awoohaw,

I have redone the formula in a different way, which has worked, I used =IFERROR(IF(K26="", "", INT(IF(M26="", N26, M26)-K26) & " Days " & HOUR(IF(M26="", N26, M26)-K26) & " h " & MINUTE(IF(M26="", N26, M26)-K26) & " m"), "")

Yeah, I can't figure out how to keep total days greater than a month either. I thought a format of [dd] could have worked, but it doesn't.
Your solution is good, but you must remember that it is now text and no longer a value.

 

[Stef9910]

Yeah, I can't figure out how to keep total days greater than a month either. I thought a format of [dd] could have worked, but it doesn't.
Your solution is good, but you must remember that it is now text and no longer a value.

Yeah, inserted a couple of helper columns to calculate minutes instead of the days hours and minutes, which allows me to work out percentages, seen as, like you the results are no longer values.

Thanks for your time awoohaw, really appreciate it