Avatar of agwalsh
agwalsh

asked on 

Calculate waiting times that spans two days

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
Microsoft OfficeMicrosoft ExcelSpreadsheets

Avatar of undefined
Last Comment
agwalsh
SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Shums Faruk
Shums Faruk
Flag of India image

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
SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of agwalsh
agwalsh

ASKER

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
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Do you need any changes?
Avatar of agwalsh
agwalsh

ASKER

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.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of agwalsh
agwalsh

ASKER

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo