Calculating Time
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
Comments
-
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.
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
