Does anyone have suggestions dealing with the...

[Kara_O_Neill]

Does anyone have suggestions dealing with the Duration type and midnight? One of the apps we built includes duration fields to track hours worked, except if the shift ends at midnight, the 0:00:00 AM timestamp will throw the hours off. I realize why it’s doing it, but wondered if there was an easier way than having them clock out at 11:59 PM and adding 1 minute to the duration or multiplying by a negative 1 if clocking out at midnight.

An example shift would be 7-3:30 and that same person worked overtime from 3:30-Midnight on the same day. The app calculates -15 hours, which it should be 15 hours (two lunch period of a half hour are included in the calculations).

[David_Hopkins]

Is there the possibility that you could use the dates in the calc?

so, you’d have Date Time 1 - Date Time 2.

I have a situation where I want to know how long it’s been since confirmation… I use:

(Now() -

[confirmed date time])

which results in something like “34:31:35”

[Grant_Stead]

@David_Hopkins I think you’ve got it… you need the whole datetime, not just the time…

[Aleksi_Alkio]

You could use something like…

IF([EndTime]>[StartTime],[EndTime] - [StartTime],“24:00:00” - ([StartTime] - [EndTime]))

[Kara_O_Neill]

@Grant_Stead@David_Hopkins I think I am suffering from post-app design anxiety honestly. As we continued on with the design, more and more complications of “what about this” keep coming up.

Changing the time field to a DataTime field would solve the problem, but also mean an update to the design of the app itself. The situation of overnight shifts doesn’t present itself all that much, heavy snowfall or hurricanes. So to redesign the app at this stage would be hard, but not impossible. Reports, workflows a billion.2 virtual columns later… I might do some testing on a dummy version of the app and see what happens.

If not, I might just need to add that bit @Aleksi_Alkio included. That might be the simplest, but no matter what I do, it will require changes in multiple places.

There are already five IFS in that duration field, whats one more?

[David_Hopkins]

@Kara_O_Neill Im not sure that you’d need to change the field type, as long as you have Date and Time (in), Date and Time (out)…

you should be able to concatenate the date and time (in) into one unit, and the Date and Time (out) into another… either in text or in a Virtual Column.

both would allow you to NOT change the primary data structure.

[Kara_O_Neill]

@David_Hopkins hmmm. That is true. Except that the time of the midnight check out technically refers to the next day’s date, which I don’t have on that record. The result would be midnight of the same day not the next day, right?

[Kara_O_Neill]

@David_Hopkins as you suspected including the data did result in a correct duration calculation. I think I might be able to get away with just having to modify two reports and changing the field to a DateTime. Thanks all for the suggestions.

[David_Hopkins]

@Kara_O_Neill interesting about the “next day” issue… I suppose you could try an if that would say … if MIDNIGHT than Date+1 … but now your starting to get into areas that could cause issues later on.

standing by