Time issue

Bar243

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

Andrée Starå

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

Bar243

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

 

Andrée Starå

Happy to help!

I'll take a look and get back to you!

Best,

Andrée

Mike Wilday

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. 

Paul Newcome

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.

Bar243

Thanks very much Paul

I will take a crack at it.

Any more help you can provide would be greatly appreciated.

 

 

Bar243

Mike, good stuff - you should have access.

Paul Newcome

Which column houses the start time? 

Bar243

Departure Time

 

 

Paul Newcome

@Bar243

 

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.

Bar243

Paul, thank you very much for the help.

Greatly appreciated.

 

 

Paul Newcome

No worries. Glad we were able to find you a solution.