Calculating Time

Kyle Lewis

Hi, 

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. 

https://app.smartsheet.com/sheets/WhCMFwrQM3735x7jW6jrX4V4CG4Crg9vj4c58RR1?view=grid

Any help would be appreciated.

Kyle 

AverageUser

Can't help on the "12:00" part since I can't even figure out how to get "Time" formats...?

But for the "Holidays" question, you can use =NETWORKDAY([Start Date],[End Date],[set an external cell reference to a column in another sheet with your holidays listed as dates])

It will exclude any of the dates included on the referenced sheet.

Paul Newcome

Kyle,

 

I have requested access. I would be more than happy to save your sheet as new so that I can fiddle with it without having to worry about messing up anything already working in the original.