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).
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โ
@David_Hopkins I think youโve got itโฆ you need the whole datetime, not just the timeโฆ
You could use something likeโฆ
IF([EndTime]>[StartTime],[EndTime] - [StartTime],โ24:00:00โ - ([StartTime] - [EndTime]))
@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?
@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.
@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?
@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.
@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
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |