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.



  • 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
    Paul Newcome ✭✭✭✭✭✭



    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!