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.

"Dan" wrote:

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

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?



"Joel" wrote:

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.

"Dan" wrote:

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

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" wrote:

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?



"Joel" wrote:

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.

"Dan" wrote:

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

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.



"Joel" wrote:

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" wrote:

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?



"Joel" wrote:

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.

"Dan" wrote:

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

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(A 2)4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A","<C"))),IF(WEEKDAY(A2)<4,"E",IF(WEE KDAY(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


"Dan" wrote in message
...
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?



"Joel" wrote:

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.

"Dan" wrote:

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

=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(A 2)4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A","<C"))),IF(WEEKDAY(A2)<4,"E",IF(WEE KDAY(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


"Rick Rothstein (MVP - VB)" wrote:

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(A 2)4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A","<C"))),IF(WEEKDAY(A2)<4,"E",IF(WEE KDAY(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


"Dan" wrote in message
...
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?



"Joel" wrote:

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.

"Dan" wrote:

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

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

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