Date/Time for Shift Start Time

[A@Ron]

Hello.

I am working in a manufacturing environment and tracking machine failures. Each machine failure will come into my database with a time stamp like:
12/27/2016 4:33:10 PM

I would like to associate a Shift to each one of these occurrences inside of a query. This will allow me to later analyze failures based on the shift (who is working). I would like to make a formula that will write the "Shift Start" time stamp like this:
12/27/2016 7:00:00 AM

Our shifts start times are either 7 AM or 7 PM, so it should take the failure time and write the last shift start time.

I am accomplished this already but I am doing it in text format which then makes a problem for my excel filters. I would like help to do this but keeping it in date/time format. Here is what I did in text format:
Shift Start: [Date] & " " & IIf(Format([wo reportdate],"hh") In (7,8,9,10,11,12,13,14,15,16,17,18),"7:00:00 AM","7:00:00 PM")
Here is [Date]:
Date: Format([Actual Finish]-(7/24),"m/d/yyyy")

Thanks for your help!

 

[MarkK]

A date is stored as a number, so you can round it to the nearest 0.5, which will yield either noon or midnight, and then offset it by subtracting 5 hours, like...

ShiftStart = DateAdd("h", -5, CLng(YourDateHere / 0.5) * 0.5)

Here's a function you can use to round a number to any other number...

Function RoundToNearest(Number As Single, Optional RoundTo As Single = 1)
   '1) divide your number by what you want to round to,
   '2) round that result, and
   '3) multiply by what you want to round to
   RoundToNearest = CLng(Number / RoundTo) * RoundTo
End Function

 

[A@Ron]

A date is stored as a number, so you can round it to the nearest 0.5, which will yield either noon or midnight, and then offset it by subtracting 5 hours, like...

ShiftStart = DateAdd("h", -5, CLng(YourDateHere / 0.5) * 0.5)

Here's a function you can use to round a number to any other number...

Function RoundToNearest(Number As Single, Optional RoundTo As Single = 1)
   '1) divide your number by what you want to round to,
   '2) round that result, and
   '3) multiply by what you want to round to
   RoundToNearest = CLng(Number / RoundTo) * RoundTo
End Function

Perfect! Thank you very much!

 

[MarkK]

You are welcome!

 

[A@Ron]

Upon further looking, I noticed that I had a small mistake on some time stamps like:
7/30/2015 6:17:01 AM gave me: 7/30/2015 7:00:00 AM. It should have given me: 7/29/2015 7:00:00 PM.

I changed the CLng function to Int (should be rounding down now). 7/30/2015 6:17:01 AM now gives me: 7/29/2015 7:00:00 PM.

But now I have other problems like:
7/7/2015 10:18:01 AM gives me: 7/6/2015 7:00:00 PM. It should give me 7/7/2015 7:00:00 AM

 

[MarkK]

Right, OK, so you need to offset the date/time before rounding too. The steps are...

Function GetShiftStart(ByVal d1 As Date) As Date
[COLOR="Green"]    'offset the time by one hour, so we round from 7 to 7, not 6 to 6[/COLOR]
    d1 = DateAdd("h", -1, d1)
[COLOR="Green"]    'round to the nearest half-day[/COLOR]
    d1 = CLng(d1 / 0.5) * 0.5
[COLOR="Green"]    'offset the time again to find shift start[/COLOR]
    GetShiftStart = DateAdd("h", -5, d1)
End Function

...and test with times 2 secs apart...

? getshiftstart(#1/1/16 6:59:59 a#)
? getshiftstart(#1/1/16 7:01:01 a#)

...but falling in different shifts.

 

[A@Ron]

Right, OK, so you need to offset the date/time before rounding too. The steps are...

Function GetShiftStart(ByVal d1 As Date) As Date
[COLOR=Green]    'offset the time by one hour, so we round from 7 to 7, not 6 to 6[/COLOR]
    d1 = DateAdd("h", -1, d1)
[COLOR=Green]    'round to the nearest half-day[/COLOR]
    d1 = CLng(d1 / 0.5) * 0.5
[COLOR=Green]    'offset the time again to find shift start[/COLOR]
    GetShiftStart = DateAdd("h", -5, d1)
End Function

...and test with times 2 secs apart...

? getshiftstart(#1/1/16 6:59:59 a#)
? getshiftstart(#1/1/16 7:01:01 a#)

...but falling in different shifts.

MarkK, you rock! Thanks for your help. This seems to work for me but I'll continue to follow it:

Shift Start: DateAdd("h",-5,(CLng((DateAdd("h",-1,[wo reportdate]))/0.5)*0.5))