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
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),"") |