Formula to determine shift
 

Hi -
You guys are always so helpful and once again I turn to you for help. I
have tried a few different formula options but none have given the desired
outcome.

I have two columns of data. One column is the date in the format
mm/dd/yyyy. The second column is the time in the hh:mm:ss AM/PM format.

What I need to do is analyze the data in both columns to determine which
shift a transaction occurred during. For example, if the Date = 8/14/2005
and the time is 7:00:00 AM, then the formula needs to return "Shift 4". I
have the shift dates in a separate worksheet and the shift start and end
times can either be written into the formula or in a validation list of some
kind.

Is this calculation possible to complete in Excel?

HELP!

Thanks!
Liz

Hi!

We'd need to know what the shifts are!

Biff

"Liz" wrote in message
...
Hi -
You guys are always so helpful and once again I turn to you for help. I
have tried a few different formula options but none have given the desired
outcome.

I have two columns of data. One column is the date in the format
mm/dd/yyyy. The second column is the time in the hh:mm:ss AM/PM format.

What I need to do is analyze the data in both columns to determine which
shift a transaction occurred during. For example, if the Date = 8/14/2005
and the time is 7:00:00 AM, then the formula needs to return "Shift 4". I
have the shift dates in a separate worksheet and the shift start and end
times can either be written into the formula or in a validation list of
some
kind.

Is this calculation possible to complete in Excel?

HELP!

Thanks!
Liz

eSorry, it gets a little tricky though because they are compressed schedules.
I have the specifics dates for each shift in a worksheet table in Excel.
However, below is the breakdown of how our shifts work.

Shift 4 = 6PM - 6PM every other Saturday and Sunday, Monday, Tuesday
Shift 5 = 6AM - 6PM Sunday, Monday, Tuesday, and every other Wednesday
Shift 6 = 6PM - 6AM Wednesday, Thursday, Friday, and every other Saturday
Shift 7 = 6AM - 6PM every other Wednesday and Thursday, Friday, Saturday

Shift

"Biff" wrote:

Hi!

We'd need to know what the shifts are!

Biff

"Liz" wrote in message
...
Hi -
You guys are always so helpful and once again I turn to you for help. I
have tried a few different formula options but none have given the desired
outcome.

I have two columns of data. One column is the date in the format
mm/dd/yyyy. The second column is the time in the hh:mm:ss AM/PM format.

What I need to do is analyze the data in both columns to determine which
shift a transaction occurred during. For example, if the Date = 8/14/2005
and the time is 7:00:00 AM, then the formula needs to return "Shift 4". I
have the shift dates in a separate worksheet and the shift start and end
times can either be written into the formula or in a validation list of
some
kind.

Is this calculation possible to complete in Excel?

HELP!

Thanks!
Liz

Hi!

Is this calculation possible to complete in Excel?

Hmmmm.....

After seeing the "shifts" I'm going to go out on a limb and say: good luck
with that!

How do you determine if a particular weekday is an "every other" weekday?

Good luck with this.

Biff

"Liz" wrote in message
...
eSorry, it gets a little tricky though because they are compressed
schedules.
I have the specifics dates for each shift in a worksheet table in Excel.
However, below is the breakdown of how our shifts work.

Shift 4 = 6PM - 6PM every other Saturday and Sunday, Monday, Tuesday
Shift 5 = 6AM - 6PM Sunday, Monday, Tuesday, and every other Wednesday
Shift 6 = 6PM - 6AM Wednesday, Thursday, Friday, and every other Saturday
Shift 7 = 6AM - 6PM every other Wednesday and Thursday, Friday, Saturday

Shift

"Biff" wrote:

Hi!

We'd need to know what the shifts are!

Biff

"Liz" wrote in message
...
Hi -
You guys are always so helpful and once again I turn to you for help.
I
have tried a few different formula options but none have given the
desired
outcome.

I have two columns of data. One column is the date in the format
mm/dd/yyyy. The second column is the time in the hh:mm:ss AM/PM
format.

What I need to do is analyze the data in both columns to determine
which
shift a transaction occurred during. For example, if the Date =
8/14/2005
and the time is 7:00:00 AM, then the formula needs to return "Shift 4".
I
have the shift dates in a separate worksheet and the shift start and
end
times can either be written into the formula or in a validation list of
some
kind.

Is this calculation possible to complete in Excel?

HELP!

Thanks!
Liz

Ha Ha! Very funny.... =)
I think I've found a manual way around that won't be too time consuming
after the initial scrub

Thanks anyway!
Liz

"Biff" wrote:

Hi!

Is this calculation possible to complete in Excel?

Hmmmm.....

After seeing the "shifts" I'm going to go out on a limb and say: good luck
with that!

How do you determine if a particular weekday is an "every other" weekday?

Good luck with this.

Biff

"Liz" wrote in message
...
eSorry, it gets a little tricky though because they are compressed
schedules.
I have the specifics dates for each shift in a worksheet table in Excel.
However, below is the breakdown of how our shifts work.

Shift 4 = 6PM - 6PM every other Saturday and Sunday, Monday, Tuesday
Shift 5 = 6AM - 6PM Sunday, Monday, Tuesday, and every other Wednesday
Shift 6 = 6PM - 6AM Wednesday, Thursday, Friday, and every other Saturday
Shift 7 = 6AM - 6PM every other Wednesday and Thursday, Friday, Saturday

Shift

"Biff" wrote:

Hi!

We'd need to know what the shifts are!

Biff

"Liz" wrote in message
...
Hi -
You guys are always so helpful and once again I turn to you for help.
I
have tried a few different formula options but none have given the
desired
outcome.

I have two columns of data. One column is the date in the format
mm/dd/yyyy. The second column is the time in the hh:mm:ss AM/PM
format.

What I need to do is analyze the data in both columns to determine
which
shift a transaction occurred during. For example, if the Date =
8/14/2005
and the time is 7:00:00 AM, then the formula needs to return "Shift 4".
I
have the shift dates in a separate worksheet and the shift start and
end
times can either be written into the formula or in a validation list of
some
kind.

Is this calculation possible to complete in Excel?

HELP!

Thanks!
Liz