Date and Time difference not calculating over 30 days

Stef9910

Board Regular
Joined
Nov 2, 2022
Messages
72
Office Version
  1. 2010
Platform
  1. Windows
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
DEFGHIJKLMNOPQ
25Received TimeStart DateStart TimeEnd DateEnd TimeRTPRTP TimeReceived DateStart Date & TimeEnd Date & TimeRTP Date & TimeDays, Hours, Minutes until StartedTotal SpanFacilitated Span
2619:1631/12/2219:451/7/2321:2031/12/2022 19:1631/12/2022 19:451/07/2023 21:20 0 days 0 h 29 m30 days 2 h 04 m30 days 1 h 35 m
2711:262/6/2312:5822/05/2023 11:26  2/06/2023 12:58 11 days 1 h 32 m 
286:3025/5/2315:157/6/2314:3023/05/2023 6:3025/05/2023 15:157/06/2023 14:30 2 days 8 h 45 m15 days 8 h 00 m12 days 23 h 15 m
290:4619/5/2320:1413/6/2320:0018/05/2023 0:4619/05/2023 20:1413/06/2023 20:00 1 days 19 h 28 m26 days 19 h 14 m24 days 23 h 46 m
3021:2525/5/2319:4024/05/2023 21:2525/05/2023 19:40  0 days 22 h 15 m  
Sheet1
Cell Formulas
RangeFormula
K26:K30K26=C26+D26
L26:L30L26=IFERROR(IF(E26+F26=DATE(1900,1,0),"",E26+F26),"")
M26:M30M26=IFERROR(IF(G26+H26=DATE(1900,1,0),"",G26+H26),"")
N26:N30N26=IFERROR(IF(I26+J26=DATE(1900,1,0),"",I26+J26),"")
O26:O30O26=IFERROR(IF(L26-K26=DATE(1900,1,0),"",L26-K26),"")
P26:P30P26=IFERROR(IF(K26="", "", IF(M26="", IF(N26=K26, "", N26-K26), IF(N26<>"", N26, M26)-K26)),"")
Q26:Q30Q26=IFERROR(IF(M6-L26=DATE(1900,1,0),"",M26-L26),"")
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Last edited:
Upvote 0
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
CDEFGHIJKLMNOPQ
25Received DateReceived TimeStart DateStart TimeEnd DateEnd TimeRTPRTP TimeReceived DateStart Date & TimeEnd Date & TimeRTP Date & TimeDays, Hours, Minutes until StartedTotal SpanFacilitated Span
2631/12/2219:1631/12/2219:451/7/2321:2031/12/2022 19:1631/12/2022 19:451/07/2023 21:20 0 days 0 h 29 m30 days 2 h 04 m30 days 1 h 35 m
2722/5/2311:262/6/2312:5822/05/2023 11:26  2/06/2023 12:58 11 days 1 h 32 m 
2823/5/236:3025/5/2315:157/6/2314:3023/05/2023 6:3025/05/2023 15:157/06/2023 14:30 2 days 8 h 45 m15 days 8 h 00 m12 days 23 h 15 m
2918/5/230:4619/5/2320:1413/6/2320:0018/05/2023 0:4619/05/2023 20:1413/06/2023 20:00 1 days 19 h 28 m26 days 19 h 14 m24 days 23 h 46 m
3024/5/2321:2525/5/2319:4024/05/2023 21:2525/05/2023 19:40  0 days 22 h 15 m  
Sheet1
Cell Formulas
RangeFormula
K26:K30K26=C26+D26
L26:L30L26=IFERROR(IF(E26+F26=DATE(1900,1,0),"",E26+F26),"")
M26:M30M26=IFERROR(IF(G26+H26=DATE(1900,1,0),"",G26+H26),"")
N26:N30N26=IFERROR(IF(I26+J26=DATE(1900,1,0),"",I26+J26),"")
O26:O30O26=IFERROR(IF(L26-K26=DATE(1900,1,0),"",L26-K26),"")
P26:P30P26=IFERROR(IF(K26="", "", IF(M26="", IF(N26=K26, "", N26-K26), IF(N26<>"", N26, M26)-K26)),"")
Q26:Q30Q26=IFERROR(IF(M6-L26=DATE(1900,1,0),"",M26-L26),"")
 
Upvote 0
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"), "")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top