Its a question of time....


Hi all,

I know questions of handling time keep coming up and I have one of my own.

I have a sheet that calculates downtime for machinery. I used some formulas Paul Newcome listed and they were a HUGE help.

What I have now is a Downtime Start date column and a Downtime Start time column and corresponding Downtime Stop Date and Downtime Stop Time columns where I display time as xx:xx

What I would like to do now is, where there is no entry in the Downtime Stop columns (ie the job is still open) I'd like to calculate the difference to the current date and time NOW, so I can show this in a report how long the job has been down for.

I could really do with a steer on how to accomplish this

Thanks in advance



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The best we could do without a 3rd party app is every hour. Basically you would set up a sheet where you have each hour listed in its own row. Then you would set up 24 Record A Date automations to run daily on each hour populating the corresponding row. Next you would have a Modified (Date) type column to capture the date/time when the rows are updated. Then you would pull the most recent from the Modified column (MAX) and pull the time out of that. Finally you would bring that date/time over to your working sheet and reference that using an IF statement imbedded into your existing calculations.

  • Dan Crimmins
    Dan Crimmins ✭✭✭✭

    That sir, is genius !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!