If Between Dates Function - please help

[Mitchell_Collen via OfficeKB.com]

Hello All, Please help me to do this.
I have a spreadsheet containing a column of dates called EntryDates, I would
like to create a function that will
check and return either shift1, shift 2 or shift 3 depending on the value in
EntryDates Column.

For instance:
If between 7:00 and 14:59 insert "shift 1" in the cell.
If between 15:00 and 22:59 insert "shift 2" in the cell.
If between 23:00 and 6:59 insert "shift 3" in the cell.

I do not know what to do at this point.
Please advise.

 

[Bob Phillips]

="Shift
"&IF(A2<TIME(7,0,0),"3",IF(A2<TIME(15,0,0),1,IF(A2<TIME(23,0,0),2,3)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Hello All, Please help me to do this.

I have a spreadsheet containing a column of dates called EntryDates, I
would
like to create a function that will
check and return either shift1, shift 2 or shift 3 depending on the value
in
EntryDates Column.

For instance:
If between 7:00 and 14:59 insert "shift 1" in the cell.
If between 15:00 and 22:59 insert "shift 2" in the cell.
If between 23:00 and 6:59 insert "shift 3" in the cell.

I am pretty sure that this formula will do that...

="Shift "&1+MOD(3+INT((24*A1-7)/8),3)

Rick

 

[Mitchell_Collen via OfficeKB.com]

This returned Shift 3 no matter what the value was. Thanks for the help. I am
going to try to work with what you have given me.
-Misty

="Shift
"&IF(A2<TIME(7,0,0),"3",IF(A2<TIME(15,0,0),1,IF(A2<TIME(23,0,0),2,3)))

Hello All, Please help me to do this.
I have a spreadsheet containing a column of dates called EntryDates, I

[quoted text clipped - 11 lines]

I do not know what to do at this point.
Please advise.

 

[Mitchell_Collen via OfficeKB.com]

Thanks Rick. This worked very well.
-Misty

Hello All, Please help me to do this.
I have a spreadsheet containing a column of dates called EntryDates, I

[quoted text clipped - 8 lines]

If between 15:00 and 22:59 insert "shift 2" in the cell.
If between 23:00 and 6:59 insert "shift 3" in the cell.

I am pretty sure that this formula will do that...

="Shift "&1+MOD(3+INT((24*A1-7)/8),3)

Rick

 

[David Biddulph]

Bob's formula returns Shift 1 for an input of 09:00, but I guess the problem
is that you've got date as well as time in your input cell? In which case,
replace each occurrence of A2 in Bob's formula by MOD(A2,1).
--
David Biddulph

This returned Shift 3 no matter what the value was. Thanks for the help. I
am
going to try to work with what you have given me.
-Misty

="Shift
"&IF(A2<TIME(7,0,0),"3",IF(A2<TIME(15,0,0),1,IF(A2<TIME(23,0,0),2,3)))

Hello All, Please help me to do this.
I have a spreadsheet containing a column of dates called EntryDates, I

[quoted text clipped - 11 lines]

I do not know what to do at this point.
Please advise.