How to calculate shift times stored as integers?

Using formulas, why would you need VBA?

Attach an example workbook

Well this is one of the  "tough" cases...
I guess the right ways is to do your calculations at first in 24h format to avoid any misconceptions
Also you need to check if the the end of shift time is smaller than the start shift..
Well a small sample would be helpful to find out the problematic ones.

i'd use a date/time field which stores the date and time
then datediff would calculate correctly for a shift from 10pm to 6am

10pm to 6am next day = 8 hours

looks like you calculate 6am to 10pm, which is 16 hours
or -16 first, and then abs, makes it +16

Hi Experts,

I'm not at work today, will test suggestions next week and keep you posted.

Have a nice weekend!

Thanks,
Ben

Hi Experts,

Attached some examples of data showing wrong calculations.

The data is a results of the following query.

SELECT Skilled_Nursing_Visit_Note.Shift_From_Hour, Skilled_Nursing_Visit_Note.Shift_From_Minute, Skilled_Nursing_Visit_Note.Shift_To_Hour, Skilled_Nursing_Visit_Note.Shift_To_Minute, Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))/60 AS Duration
FROM Skilled_Nursing_Visit_Note
WHERE (((Skilled_Nursing_Visit_Note.Shift_From_Hour)=23));

Select allOpen in new window

@Gustav,
If not mistaken, the current formula is something you helped me put together a while back..-:)
Tried yours now and just returning true and false results (-1,0).

Thanks,
Ben
Book1.xlsx

Well, use the code I posted, and it will work.

@Gustav,

I will have users testing it and keep you posted.

Thanks,
Ben

I would  check out myself first  - quite easy to do.

what if someone wanted to break a record and works 49 hours ?
from Monday 09:00 to Wednesday 10:00

someone might get angry if you'd only pay 1 hour

your design is flawed without the actual date
dunno why nobody makes you intent on that, and nothing you program without that date will ever be completely correct

you don't have to take my word for it
it's like driving a car without wheels and a steering wheel, it will never work

@Greek,
I guess there is no schedule app surpassing 24 hours, or they will create 2+ records for that..
Thanks,
Ben

Thank you!

You are welcome!