Its a question of time....

Dan Crimmins

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

Dan

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

That sir, is genius !