Time formatting... (1 Viewer)

[QMDirk]

I have a form that tracks when the production line stops, and when it is restarted. A 3rd (calculated) field displays the difference. a 4th field Sums up all the downtime and displays the total in the footer. Everything was working fine, until I noticed that if the line stops before midnight, and restarts AFTER midnight, the answer box displays the value as a negative number. All the time-formats in my program are on 24-hour, or military time. So, a stop time of 23:30, with a restart time of 01:15, 1 hour and 30 minutes later, displays "-1350". (24 hours x 60 minutes = 1440 minutes; 1350 minutes + 90 minutes DT = 1440). I get why, but here's there problem.

the object names are: [Time Down], [Time Restart], [DTMinutes], and [Text24].

I started with this code: =DateDiff("n",[Time Restart],[Time Down])*-1 in [DTMinutes] to display the difference in times. It works, but shows a negative on any line when crossing midnight.
Then I used: =Sum(DateDiff("n",[Time Restart],[Time Down])*-1) in [Text24] to Sum the downtime in the footer. As long as none of the [Time Restart] values cross midnight, the individual times display correctly and so does the total. But, if any of the entries crosses midnight, [DTMinutes] goes negative on that line, and [Text24] goes negative and grows smaller since it is now subtracting the total downtime minutes from 24 hours (1440) instead of adding them.

So, I found this code:

=Format([StartTime] -1 -[EndTime], "Short Time") This line solves the issue But now the answer displays in this format: hh:mm instead of just 'minutes' (shows 01:30, instead of 90). Does anyone know how I can get my format back to display the difference, and total difference, in minutes ONLY and not hours AND minutes? Thanks.

 

[theDBguy]

Hi. Just curious, what happens if you try it this way?

=Format([StartTime] -1 -[EndTime], "nn")

 

[plog]

Everything was working fine, until I noticed that if the line stops before midnight, and restarts AFTER midnight, the answer box displays the value as a negative number

Sounds like you aren't storing enough data. If something can span days, then you also need to store dates as well as time. It's a called a DateTime field for a reason--it can hold both. And when you do, there's no need for hacking negative numbers into something usable.

 

[QMDirk]

Hi. Just curious, what happens if you try it this way?

=Format([StartTime] -1 -[EndTime], "nn")

It shows the minutes correctly but drops the hour value. And 08:00 to 10:00 displays as 0 instead of 120.

 

[theDBguy]

It shows the minutes correctly but drops the hour value. And 08:00 to 10:00 displays as 0 instead of 120.

Sounds like you'll have to use some math then, or a function.