Please help with a date/time query!

[batman07]

Everyone, I am trying to build a database to assist with assigning
employees to shift codes. I have built what I needed in Excel, but
found that Access handles dates/times much differently.

Parameters:
Less than 50% of hours after 5pm is shift 1.
50% or more of hours after 5pm is shift 2.
50% or more of hours from midnight to 6am is shift 3.
Double day weekends for a 8x5, 16 hours (or 40% of hours on the
weekend) is shift 5.
Double day weekends for a 10x4, 20 hours (or 50% of hours on the
weekend) is shift 9.
40% of hours from 9pm Fri to 8am Mon is shift 6.
50% of hours from 9pm Fri to 8am Mon is shift 0.

Definitions:
DAY _0_START = 39815.xxxx
DAY_0_STOP = 39815.xxxx
M_Midnight = Monday Midnight. (39817.999999)

Times Times Times Times Times
M_Midnight M_6am M_8am M_5pm M_9pm
39817.999999 39818.25 39818.334 39818.709 39818.875

Select Query Formula:
Mon_Start: IIf([DAY_0_START]=0,"Off",(DateDiff("n",[M_Midnight],
[DAY_0_START])/60*100))
Mon_Stop: IIf(DateDiff("n",[M_Midnight],[DAY_0_Stop])<0,"Off",IIf
(DateDiff("n",[M_Midnight],[DAY_0_Stop])/60*100>2400,DateDiff("n",
[M_Midnight],[DAY_0_Stop])/60*100-2400,DateDiff("n",[M_Midnight],
[DAY_0_Stop])/60*100))

This works to get me a whole number, but the display isn’t quite
right. Ex. 13:30 = 1350, 8:00 = 1333.33

I just need to figure out how to format the date and time so that it
can be used against the thresholds above.
Can anyone assist me with this? Any and all help will be greatly
appreciated!

 

[Fred]

I noticed that nobody answered. If yo will forgive a response that sounds a
little rough in an attempt to be helpful.

The "info" in your post consisted on some definitions of shift terminology
plus a boatload of unexplained details on what you tried. And you made no
statements on what is (overall) going on or what specifically you are trying
to do.

 

[Pavel]

http://pashtet.webnode.com/