Calculate waiting times that spans two days

agwalsh
agwalsh used Ask the Experts™
on
hi
I'm attaching a file that shows the following - date, start time, finish time. If the start and finish times are within the same day, it's fine - but how do I show the time waiting if it spans a couple of days. Does the person entering have to enter the complete start date with start time and ditto for finish date and time or can they some how incorporate the date into the formula? Thanks
Time_and_date_question.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
HI,

pls try
=+IF(C4<B4,1+C4,C4)-B4

Open in new window

shorter
=IF(C4<B4,1)+C4-B4

Open in new window

Regards
Even shorter
=C4-B4+(C4<B4)

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Rather I would use full date & time and use below formula:
=IF(OR($B7="",$C7=""),"",IF(TEXT(C7,"d")-TEXT(B7,"d")>=1,DAY($C7-$B7)&" Days "&HOUR(MOD(C7-B7,1))&" Hours"&" Waiting Time",HOUR($C7-$B7)&" Hours"&" Waiting Time"))

Open in new window

Please find attached...
Time_and_date_question_v1.xlsx
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
If you want to be more specific between 23 hours and days, then use below:

=IF(OR($B7="",$C7=""),"",IF(INT((C7-B7)*24)=24,DAY($C7-$B7)&" Days"&" Waiting Time",IF(INT((C7-B7)*24)>24,DAY($C7-$B7)&" Days "&HOUR($C7-$B7)&" Hours "&"Waiting Time",HOUR($C7-$B7)&" Hours"&" Waiting Time")))
Please find attached...
Time_and_date_question_v2.xlsx
Finance Analyst
Commented:
Including Start Date and Finish Date would make the formula much simpler.

Start Date in column A
Start Time in Column B
End Date in column C
End Time in column D

Formula:

=(C3+D3)-(A3+B3)

Result with custom format [hh]:mm. This custom format will allow the hours to go over 24 when more than one day.

See attached.

Thanks
Rob
Time_and_date_question.xlsx

Author

Commented:
The person I am helping is not an experienced Excel person and my suggestion to her was actually the Rob Henson one but I just wanted to check that there wasn't something fairly obvious and not too complex that I was missing.  Good answer @Shums - but I *know* she won't use that and while there seems to be more work in adding an extra column, I reckon that's the way to go...Thank you both
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Do you need any changes?

Author

Commented:
Got lots of "ferrari" solutions but ended up going with a slighter longer winded set up but which I know would be easier for the user to understand. thanks to all.
Rob HensonFinance Analyst

Commented:
Thanks for the feedback.

If the waiting time will only ever be 24 hours or less, albeit the finish time could be the next day, there could be another option whereby you can use the existing set-up with only the start date.

Start Date in Column A
Start Time in Column B
End Time in column C

=IF(C3>B3,C3-B3,(A3+1+C3)-(A3+B3))

See attached.

The first suggestion would not take weekends into account, ie starting something on a Friday afternoon but not finishing until Monday morning. For this scenario, the first suggestion would add 48 hours for the weekend whereas this suggestion would assume it is the next day.

Thanks
Rob H
Time_and_date_question.xlsx

Author

Commented:
The weekend is not an issue in this scenario. And the waiting time could possibly go on longer - but thanks :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial