We help IT Professionals succeed at work.

Calculate waiting times that spans two days

123 Views
Last Modified: 2017-04-03
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

CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Saqib HusainEngineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Finance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT

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 :-)