Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum Access Troubleshooter Access Developer Network Access AI Bot ------------ Excel Courses Excel Index ------------ Code Vault Testimonials Tip Jar Advice for Consultants

 Home > TechHelp > Directory > Access > Date Math < Normalizing Data | Quick Access Toolbar >
 Back to Date Math    Comments List
 Kerry Smyth         2 years ago
I am struggling trying to design a function that calculates the current shift at any given time or day. If you don't extract the day portion of the datetime, the calculation doesn't work. If X is a certain datetime it will always be greater than any given time since time alone is stored as a number less than 1. So far I have been able to do a workaround with the formula, [MyDate] - INT([MyDate]), which leaves me with the serial number for the time, but I am hoping there is a more eloquent method. In case you were wondering, I am trying to use a query to determine what SHIFT certain jobs were performed. I have the datetime on each record and I'm trying to use a query to create a new field that tells me which shift the job was completed. What's worse is that I have to compound this with the fact that we have two halves of the week, which means we have a total of 6 shifts per week, 1st 2nd and 3rd shifts for both front half and back half of the week. Thanks!
 Kevin Robertson              2 years ago Not really sure what you are asking. Do you want to display the actual time (without the date) instead of the number? That can be done easily with the `TimeSerial() `function. `TimeOnly: TimeSerial (Hour (MyDate), Minute (MyDate), Second (MyDate))` Some screenshot may help us to understand your needs better. The following all deal with times: Time & Billing Time as a Duration Timesheets Timestamp
 Adam Schwanz               2 years ago There's also TimeValue(mydate)
 Kevin Yip        2 years ago Hi Kerry, you need some kind of Shift table that stores start date/time and end date/time of each of the 6 shifts in a week.  Then it would be a simple use of DLookup() to look up what shift matches a given date and time.  Naturally you need to use full date/time info in all your values and variables for this task.  If you need to keep historical records of the shifts, include the year info in the date/time values too.  That way, there would be no mistaking of one shift for another:
 Kevin Yip        2 years ago
 Kerry Smyth         2 years ago Let me try to clarify - instead of making "ShiftName" a field that I have to enter on each task record (meaning, what shift did this task get completed on), I realized that with the "Completed" DateTime already entered on the record, the shift that it was completed can simply be calculated. But that is WAY easier said than done. Believe it or not I actually got it to calculate correctly but the formula is an absolute monstrocity. I'll copy and paste it at the bottom so you can try it. So let's imagine a company that has 3 shifts, Day, Mid, and Night shift. But each crew only works half the week, front half or back half, and on Wednesdays both front half and back half work together all day. That leaves you with 6 total shifts: Front half days (FHD), Front half mids (FHM), Front half nights (FHN), and the same for back half - (BHD, BHM and BHN). Now imagine the first shift of the week starts X hours after midnight. In my case, the first shift of front half days starts at 7:0AM, or 7 hours after midnight. Using IIF statements with date and days of the week work a lot better if you start Sunday at midnight, but we don't. So I cheated and offset the week by 7 hours by subtracting that amount from the actual DateTime value. After that I used a labyrinth if nested IIF statements in the query to make it work. I called the new field "Shift". Here is the function I used: Shift: IIf(CDate(([DateTimeInput]-7/24)-Int([DateTimeInput]-7/24))>=TimeSerial(16,0,0),IIf(Weekday(CDate([DateTimeInput]-7/24))>4,"BHN",IIf(Weekday(CDate([DateTimeInput]-7/24))<4,"FHN","FHN/BHN")),IIf(CDate(([DateTimeInput]-7/24)-Int([DateTimeInput]-7/24))4,"BHD",IIf(Weekday(CDate([DateTimeInput]-7/24))<4,"FHD","FHD/BHD")),IIf(Weekday(CDate([DateTimeInput]-7/24))>4,"BHM",IIf(Weekday(CDate([DateTimeInput]-7/24))<4,"FHM","FHM/BHM")))) In words this basically says, if the time is later than 16 hours after the start of Day shift and the day of the week is after Wednesday, it's BHN shift, else if its before Wednesday, then its FHN shift, otherwise it's Wednesday and therefor both shifts were working. Then it repeats this for if the DateTime is less than 8 hours after the start of Day Shift, checks the day of the week and assigns FHD, FHM or FHN, or both, then repeats one more time for Mid shift. What I am hoping is there is something better or easier to accomplish this?
 Kevin Yip        2 years ago Hi Kerry, your formula is a pretty ingenious creation for what it is.  If you want to simplify it, you need to simplify the underlying methodology.  I think you need to add one key info: the date and time of the start of the first shift of the week, FHD.   Based on the info you have posted, day shift is 7am to 3pm, mid-shift is 3-11pm, and night shift is 11pm-7am.  And I'm assuming "front" is Mon to Wed, and "Back" is Wed to Fri. Let's use the current week as an example.  The start of this week's FHD is (was) Monday 10/10/22 7am.  Let's call this date value d. Then: FHD is between d and d + 8 hours, OR between d + 24 hrs and d + 32 hours. FHM is between d + 8 and d + 16 hours, OR between d + 32 hrs and d + 40 hours. FHN is between d + 16 and d + 24 hours, OR between d + 40 hrs and d + 48 hours. FHD/BHD is between d + 48 hrs and d + 56 hours. FHM/BHM is between d + 56 hrs and d + 64 hours. FHN/BHN is between d + 64 hrs and d + 72 hours.      And so on for BHD, BHM, and BHN.  You get the picture.  That makes the ensuing formula much simpler. Also, as you have figured out, the Access date value of N hours equals N/24 (i.e. Access stores 8 hours as 8/24, or 0.3333). If a given date and time is C, then you can find out what shift C belongs to with the Switch() function, which Richard has made a video about.  In short, Switch() checks for a series of conditions from left to right, and returns the proper value: =Switch(cond1, value if cond1 is true, cond2, value if cond2 is true, ... ... , value if all previous conditions are false) So your formula is: =Switch(C >= d AND C < d+1/24, "FHD", C >= d=24/24 AND C < d + 32/24, "FHD", C >= d+1/24 AND C < d+16/24, "FHM", C >= d+32/24 AND C < d+40/24, "FHM", C >= d+16/24 AND C < d+24/24, "FHN", C >= d+40/24 AND C < d+48/24, "FHN", ... ... , "") It still looks formidable, but at least the syntax is easier to follow.
 Kerry Smyth         2 years ago Thank you! I never thought of a Switch function and I will go look up what Richard has taught about it.
 Kerry Smyth         2 years ago In honor of Rick's latest "fast tips" video on TimeValue, I thought I would clean up my formula above. Again, I had to come up with a Calculated field for [Shift] where the work week is divided up into 6 total crews - 3 of them Sunday through Wednesday and the other 3 Wednesday through Saturday. Both crews work simultaneously on Wednesday. The shift names are abbreviations for "Front Half" or "Back Half" Day, Mids, and Nights. The whole formula is below if you want to copy and paste it to try yourself, or modify it for your own uses. Shift: IIf(TimeValue([DateTimeInput]-7/24)>=TimeSerial(16,0,0),    //Checks to see if night shift     IIf(Weekday([DateTimeInput]-7/24)>4,                             //If Night Shift, checks to see if after Wed         "BHN",                                                                       //Is Night Shift, and after Wednesday         IIf(Weekday([DateTimeInput]-7/24)<4,                         //Is Night Shift, checks to see if before Wed             "FHN",                                                                   //Is Night Shift, and before Wednesday             "FHN/BHN")),                                                         //Is Night Shift on Wednesday     IIf(TimeValue([DateTimeInput]-7/24)4,                         //Is Day shift, checks to see if after Wed             "BHD",                                                                   //Is Day shift after Wednesday             IIf(Weekday([DateTimeInput]-7/24)<4,                     //Is Day Shift, checks to see if before Wed                 "FHD",                                                               //Is Day Shift before Wednesday                 "FHD/BHD")),                                                     //Is Day shift on Wednesday         IIf(Weekday([DateTimeInput]-7/24)>4,                         //Is not Day or Night, checks if after Wed             "BHM",                                                                   //Is not Day or Night, but after Wednesday             IIf(Weekday([DateTimeInput]-7/24)<4,                     //Is Not Day, Night, checks if before Wed                 "FHM",                                                               //Is not Day or night but before Wed                 "FHM/BHM"))))                                                   //Is not Day, night, but is Wednesday Whole formula: Shift: IIf(TimeValue([DateTimeInput]-7/24)>=TimeSerial(16,0,0),IIf(Weekday([DateTimeInput]-7/24)>4,"BHN",IIf(Weekday([DateTimeInput]-7/24)<4,"FHN","FHN/BHN")),IIf(TimeValue([DateTimeInput]-7/24)4,"BHD",IIf(Weekday([DateTimeInput]-7/24)<4,"FHD","FHD/BHD")),IIf(Weekday([DateTimeInput]-7/24)>4,"BHM",IIf(Weekday([DateTimeInput]-7/24)<4,"FHM","FHM/BHM"))))

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Date Math.