Time issue
Hi All
Please can someone help me with a logistics related time question. I know Time management is limited in smartsheet however I am hoping someone has a clever solution.
We have a column for 'time leaving depot' which is inputted as a time 10:21 for instance.
The next field is 'estimated travel time', in hours:minutes. An example is 14:00.
Where I need the help is there are 'non driving hours' from 23:00 to 04:00 the next morning.
So I need a formula that looks up the departure time, takes the driving hours into account and the non driving time and confirms the estimated time of delivery of in the above example 05:21.
Hope that all makes sense.
I have used the long formula in this post to work out the loading time however have come stuck on what to do next: https://community.smartsheet.com/discussion/using-start-and-end-time-columns-calculate-hours-worked-timesheet-page
If anyone has any ideas I am very keen to hear please. Thank you
Comments
-
Hi,
I got an idea on how to solve it, and I would be happy to take a look.
Could you maybe share the sheet(s) or some screenshots? That would make it easier to help.
I hope this helps you!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée
Thanks very much.
I wasnt sure how to share, here is the link if you request access I will approve:
https://app.smartsheet.com/b/home?lx=GK8Ujx_dTqpBtEcbZSjmUg
-
Happy to help!
I'll take a look and get back to you!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I'd like to see how Andree and you handle this situation as well. Can I have a look at the sheet too? I already requested access.
-
I will say this much... That super long formula is written for a 12 hour clock with "am" and "pm" being used. You could save a lot of time and energy and avoid numerous typos by using something much shorter along with formulas to parse out the time and then convert it all to minutes. You can do your calculations from there, then use some pretty basic formulas to put it all back together in the correct format. It requires a few extra helper columns, but in the end is a much easier approach than trying to fight with that massive mess just to take into account something you aren't even using.
I too have requested access.
-
Thanks very much Paul
I will take a crack at it.
Any more help you can provide would be greatly appreciated.
-
Mike, good stuff - you should have access.
-
Which column houses the start time?
-
Departure Time
-
I saved your sheet as new and fiddled with it that way. I shared my version to you. Let me know what you think.
UPDATE:
I changed the formula in the DriveStart column to pull from your departure time so as to maintain your formatting.
-
Paul, thank you very much for the help.
Greatly appreciated.
-
No worries. Glad we were able to find you a solution.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives