Calculating Time

Kyle Lewis
Kyle Lewis ✭✭✭✭
edited 12/09/19 in Formulas and Functions


I know Smartsheet is "working" on time stamps, but I need a little help. I am trying to calculate 2 different time scenarios. First is from the time a work order is opened to the time its is dispatched. Second is the time from the open date to completed date.

You will see on my sheet, the "open to dispatch" time in minutes and the "open ~ dispatch" in D:H:M. Also you will see the same for "open ~ complete" and "open to complete". 

Those formulas seem to be working fine, except when I type "12:00", something happens that isn't right. However, if I type "12:00 pm" it corrects itself. 

My main problem is only wanting to take into account ONLY our normal business hours, 7 am-3:30 pm M-F. I only want the time to be calculating when it is between those hours, if the time is outside those hours I would need it to roll to the next business day and start counting at 7 am.

I have weekends excluded already, but I need to exclude our holidays as well.

Any help would be appreciated.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!