Subtract break and lunch time when Time Calculation past midnight

[ramirezx@ddmfg.com]

Hi,

I hope someone can help me on this, I have two shifts, 1st Shifts 6 am to 6 pm, and 2nd Shift 6 pm to 6 am. I have no problem subtracting from the 1st shift break/lunch times. It's when I'm trying to subtract from the 2nd shift which goes past midnight.


2nd Shift 1st Break is from 8:30:00 PM to 8:45:00 PM / Lunch 12:00:00 AM to 12:30:00 AM / 2nd Break 3:00:00 AM to 3:15:00 AM

Fields in my Access query:

1stBreak: IIf(([StartTime]<=#8:30:00 PM# And [EndTime]>=#8:45:00 PM#),15,0)
Lunch: IIf(([StartTime]<=#12:00:00 AM# And [EndTime]>=#12:30:00 AM#),30,0)
2ndBreak: IIf(([StartTime]<=#3:00:00 AM# And [EndTime]>=#3:15:00 AM#),15,0)

It only happens when I pass midnight between StartTime and EndTime as you can see below? The user can start and end a job at 11 pm to 6 am. I want to get it to add the break time depending on the StartTime and EndTime?

Not adding break and lunch time in RED

StartTime EndTime 1stBreak Lunch 2ndBreak 8:30 PM 8:50 PM 15 0 0 12:00 AM 12:35 AM 0 30 0 3:00 AM 3:20 AM 0 0 15 6:00 PM 6:00 AM 0 0 0 8:30 PM 3:15 AM 0 0 0 6:00 PM 12:00 AM 0 0 0 11:00 PM 6:00 AM 0 0 0

This is how I would like it to adding break and lunch time in BLUE

StartTime EndTime 1stBreak Lunch 2ndBreak 8:30 PM 8:50 PM 15 0 0 12:00 AM 12:35 AM 0 30 0 3:00 AM 3:20 AM 0 0 15

6:00 PM	6:00 AM	15	30	15
8:30 PM	3:15 AM	15	30	15
6:00 PM	12:00 AM	15	0	0
11:00 PM	6:00 AM	0	30	15

I hope someone can help? I've tried everything I could think of?

Thanks,
Xavier Ramirez

[CJ_London]

date and time is stored as a decimal number - the bit before the decimal point represents the date, where 31/12/1899 is zero, 1/1/1900 is 1 and counting up to the present day and beyond. The bit after the decimal point represents the time and is expressed as the time in seconds divided by 86400 (the number of seconds in a day).

What you see as a date and/or time is just a format, it is not the value.

So given that information, you need to check what your actual values are. You can do so by using the cdbl function. Cdbl(mydate) with return a decimal number. So if you are trying to add 15 to a time, you are adding 15 days, not minutes.