How do I convert date and time into shift worked?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to convert a date and time into a specific letter. I have the date is
in one column and the time is in another, and need a letter output in a third
column. I need the conversion to look something like this:
Every Sunday,Monday,Tuesday from 07:00 to 19:00= A
Every Sunday,Monday,Tuesday from 19:00 to 07:00= E
Every other Wednesday from 07:00 to 19:00 toggles between A and C
Every Thursday,Friday,Saturday from 07:00 to 19:00= C
Every Wednesday,Thursday,Friday from 19:00 to 07:00=F
Every other Saturday from 19:00 to 07:00 toggles between A and C
 
To convert a date and time to a text string use the TEXT function

MyTime = Text(A1,"HH:MM:SS")
MYDate = Text(A2,"MM/DD/YY")
or
MYDate = Text(A2,"MM/DD/YYYY")

You can make your own custom format. The function uses your own separator
like /,.: and just substitues the HH,MM,SS,DD,YY,YYY with actual values.
 
Joel, thanks for the help. The function you provided will not do what I am
looking for. I am looking to relate a date and time to a shift worked
(A/C/E/F). All I have is the date and time, so I need a function that will
reference both and then give the shift worked output. This needs to be a
function that I can copy down a column.
Example:
B3=8/19/2007
C3=8:30:00 AM
D3= Shift worked

This is Sunday between 07:00 and 19:00 so output should be "A Shift"
If the time were Sunday @ 19:01, the output should be "E Shift"

Can you help set something like this up?
 
The process I worked out is a little complicated, but it is the most
vertsitile. Shifts are probably different on the weekend than the week day
so a fixed formula will work out to be very complicated. I think it is
easier to do with buildingg a table. Make Sunday Midnight (Saturday night)
as hour 0 and then calculate the number of hours from Sunday midnight to the
hour of the shift. then lookup the hour in a table using vlookup. Here is
my table. Your table can be different.

Start Time Shift
0:00:00 Sunday 0:00 E
8:00:00 Sunday 8:00 D
24:00:00 Sunday 16:00 D
24:00:00 Monday 0:00 E
32:00:00 Monday 8:00 A
48:00:00 Monday 16:00 B
48:00:00 Monday 0:00 C
56:00:00 Tuesday 8:00 A
72:00:00 Tuesday 16:00 B
72:00:00 Tuesday 0:00 C
80:00:00 Wednesday 8:00 A
96:00:00 Wednesday 16:00 B
96:00:00 Wednesday 0:00 C
104:00:00 Thursday 8:00 A
120:00:00 Thursday 16:00 B
120:00:00 Thursday 0:00 C
128:00:00 Friday 8:00 A
144:00:00 Friday 16:00 B
144:00:00 Friday 0:00 C
152:00:00 Saturday 8:00 D
168:00:00 Saturday 16:00 D
168:00:00 Saturday 0:00 E


The table above have 4 columns starting in cell A1.

1) Column B - is just text information showing the day of the week
2) Column C - The start ime of the shift formated in time format
3) Column D - the shift letter which is is text. No formula
4) Column A - Formated in time format 38:00 which is total hours
this is a formula.
Cell A2 =C2
Cell A3 = A2 + C3
Then copy this formula down column A.
Column A is now total hours from Sunday midnight

Then if your shift start time is cell D1, and Date E1

the day of the week is function is weekday() which return 1 for Sunday.
Because you want Sunday to be 0 you have to subtract 1 one from this number.
=24*(weekday(E1)-1) which give the number of hours from Sunday to midnight
of the day you work
Now you have to add the time of the day which is simply your start time.
Excel throw in a simple problem that time is a fractional number of a day.
8:00 AM is really .3333333 (8 hours /24 hours). Your formula is now
=(24*(weekday(E1)-1))+(24*D1)

To make thing easier I would put this number in its own cell. For this
example F1. You could make it part of a larger formula.

Now the rest is a simple vlook() function.

=VLOOKUP(F1,A2:D33,4)

This simple says to look up the hour in the table and return the 4 column
which is column D.
 
Dan: The table in my previous posting had an error. Use these instruction
instead

The process I worked out is a little complicated, but it is the most
vertsitile. Shifts are probably different on the weekend than the week day
so a fixed formula will work out to be very complicated. I think it is
easier to do with buildingg a table. Make Sunday Midnight (Saturday night)
as hour 0 and then calculate the number of hours from Sunday midnight to the
hour of the shift. then lookup the hour in a table using vlookup. Here is
my table. Your table can be different.

Start Time Shift
0:00:00 Sunday 0:00 E
8:00:00 Sunday 8:00 D
16:00:00 Sunday 16:00 D
24:00:00 Monday 0:00 E
32:00:00 Monday 8:00 A
40:00:00 Monday 16:00 B
48:00:00 Monday 0:00 C
56:00:00 Tuesday 8:00 A
64:00:00 Tuesday 16:00 B
72:00:00 Tuesday 0:00 C
80:00:00 Wednesday 8:00 A
88:00:00 Wednesday 16:00 B
96:00:00 Wednesday 0:00 C
104:00:00 Thursday 8:00 A
112:00:00 Thursday 16:00 B
120:00:00 Thursday 0:00 C
128:00:00 Friday 8:00 A
136:00:00 Friday 16:00 B
144:00:00 Friday 0:00 C
152:00:00 Saturday 8:00 D
160:00:00 Saturday 16:00 D
168:00:00 Saturday 0:00 E


The table above have 4 columns starting in cell A1.

1) Column B - is just text information showing the day of the week
2) Column C - The start ime of the shift formated in time format
3) Column D - the shift letter which is is text. No formula
4) Column A - Formated in time format 38:00 which is total hours
this is a formula.
Cell A2 =C2
=IF(C3-C2>=0,C3-C2+A2,1+C3-C2+A2)
Then copy this formula down column A.
Column A is now total hours from Sunday midnight
the one in the above formula add 1 day so you don't get a negative number.
Time in excel for 8:00 AM is really the number .33333 which is 1/3 of a day.

Then if your shift start time is cell D1, and Date E1

The day of the week is function is weekday() which return 1 for Sunday.
Because you want Sunday to be 0 you have to subtract 1 one from this number.
=24*(weekday(E1)-1) which give the number of hours from Sunday to midnight
of the day you work
Now you have to add the time of the day which is simply your start time.
Excel throw in a simple problem that time is a fractional number of a day.
8:00 AM is really .3333333 (8 hours /24 hours). Your formula is now
=(24*(weekday(E1)-1))+(24*D1)

To make thing easier I would put this number in its own cell. For this
example F1. You could make it part of a larger formula.

Now the rest is a simple vlook() function.

=VLOOKUP(F1,A2:D33,4)

This simple says to look up the hour in the table and return the 4 column
which is column D.
 
Give this formula a try (it assumes your first data row is 2)...

=IF(A2="","",IF(AND(MOD(B2,1)>TIME(7,0,0),MOD(B2,1)<=TIME(19,0,0)),IF(WEEKDAY(A2)<4,"A",IF(WEEKDAY(A2)>4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A>","<C>"))),IF(WEEKDAY(A2)<4,"E",IF(WEEKDAY(A2)<7,"F",IF(MOD(ROUNDUP(A2/7,0),2),"<<A>>","<<C>>")))))

Note... I left the alternating week codes surrounded by angle brackets (a
single angle bracket for the alternating Wednesdays and a double angle
bracket for the alternating Saturdays. I did this because I didn't know
which code to assign initially to which alternating week (for either
alternating day value), so I figured you might need to find them easily in
order to reverse the codes if necessary. Once you have the right codes in
the right alternating weeks, simply remove the angle brackets altogether. By
the way, I used this function call, MOD(ROUNDUP(A2/7,0),2), which determines
if the week number from date-zero is even or odd, for each alternating day
test.

Rick
 
=IF(A2="","",IF(AND(MOD(B2,1)>TIME(7,0,0),MOD(B2,1)<=TIME(19,0,0)),IF(WEEKDAY(A2)<4,"A",IF(WEEKDAY(A2)>4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A>","<C>"))),IF(WEEKDAY(A2)<4,"E",IF(WEEKDAY(A2)<7,"F",IF(MOD(ROUNDUP(A2/7,0),2),"<<A>>","<<C>>")))))

This is a great formula! Has issues getting the correct shift on the pivot
days. The shifts are like this:

Every Sunday/Monday/Tuesday and every other Wednesday 07:00 to 19:00 is
A-shift
Every other Wednesday/Thursday/Friday/Saturday 07:00 to 19:00 is C-shift
Every other Saturday/Sunday/Monday/Tuesday nights from 19:00 to 07:00 is
E-shift
Every Wednesday/Thursday/Friday and every other Saturday 19:00 to 07:00 is
F-Shift
 
Every Sunday/Monday/Tuesday and every other Wednesday 07:00 to 19:00 is
A-shift
Every other Wednesday/Thursday/Friday/Saturday 07:00 to 19:00 is C-shift
Every other Saturday/Sunday/Monday/Tuesday nights from 19:00 to 07:00 is
E-shift
Every Wednesday/Thursday/Friday and every other Saturday 19:00 to 07:00 is
F-Shift
Click to expand...

This isn't what you said in your first post, is it??? You are now showing
days other than Wednesday and Saturday alternating... is that correct? Also,
your first chart showed Saturdays alternating between A and C, not E and F.
Can you lay out another chart like the first one showing exactly what you
want? Please check what you post carefully... the formulas we develop are
hand-crafted to the information you ask for... they are not always easily
changed around to account for typos.

Rick
 
Sorry about the confusion. Basically...day shifts (A/C) alternate working
Wednesdays (07:00 to 19:00), and night shifts alternate working Saturday
nights (E/F) 19:00 to 07:00.
 
Okay, I think the only (major) thing "wrong" with my original formula is the
code for the alternating Saturdays. Try this...

=IF(A2="","",IF(AND(MOD(B2,1)>TIME(7,0,0),MOD(B2,1)<=TIME(19,0,0)),IF(WEEKDAY(A2)<4,"A",IF(WEEKDAY(A2)>4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A>","<C>"))),IF(WEEKDAY(A2)<4,"E",IF(WEEKDAY(A2)<7,"F",IF(MOD(ROUNDUP(A2/7,0),2),"<<E>>","<<F>>")))))

Remember, though, for the alternating Wednesdays, you may want the <A> and
<C> codes reversed depending on which week <A> starts on. Same for the
alternating Saturdays... you may want the <<E>> and <<F>> codes reversed
depending on which week <<E>> starts on. As I said, you can identify these
two conditionals easy enough by the single angle brackets and double angle
brackets. If the alternating codes for either or both of these are coming up
reversed from what you want, simply swap the <A> and <C> with each other
and/or the <<E>> and <<F>> with each other. After you have them in the right
order, just remove all the angle brackets from the first equation and copy
that down as far as you like.

Rick
 
Rick- I sure appreciate your help on this. The days alternate perfectly as
you have it, the only issue is on Tuesday night/Wednesday morning at 00:00
the shift switches from E to F (should stay E thru 07:00). Same thing happens
on Friday night/Saturday morning at 00:00,shift switches from F to E (Should
stay F shift thru 07:00). Each shift is 12 hr shift from 19:00 to 07:00.

Thanks again
 
I haven't forgotten about you... I'll look at fixing this when I wake up.

Rick
 
While I am sure this formula can be made much trimmer, here is a modified
(read that as **highly** patched) formula which I believe corrects the
problems you pointed out and one problem that you didn't mention (every
other week, the alternate code after midnight was incorrect). The formula is
ugly, but I am pretty sure it works.

=IF(A1="","",IF(AND(MOD(B1,1)>TIME(7,0,0),MOD(B1,1)<=TIME(19,0,0)),IF(WEEKDAY(A1)<4,"A",IF(WEEKDAY(A1)>4,"C",IF(MOD(ROUNDUP(A1/7,0),2),"<A>","<C>"))),IF(AND(OR(WEEKDAY(A1)<4,AND(WEEKDAY(A1)=4,B1<TIME(7,1,0))),NOT(AND(WEEKDAY(A1)=1,B1<TIME(7,1,0)))),"E",IF(AND(OR(WEEKDAY(A1)<7,AND(WEEKDAY(A1)=7,B1<TIME(7,1,0))),NOT(AND(WEEKDAY(A1)=1,B1<TIME(7,1,0)))),"F",IF(MOD(ROUNDUP((A1-(WEEKDAY(A1)=1))/7,0),2),"<<E>>","<<F>>")))))

As before, once you decide it is working correctly, simply remove the angle
bracket.

I have an idea on a method to trim this down considerably. If this idea pans
out, I will post the revised formula here... so keep checking back into this
thread for, say, the next week to see if I succeeded or not.

Rick
 
Back
Top