If Between Dates Function - please help
 

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

If Between Dates Function - please help
 

="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)

"Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote in message
news:73b215fe2de26@uwe...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

If Between Dates Function - please help
 

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

If Between Dates Function - please help
 

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

Bob Phillips wrote:
="Shift
"&IF(A2<TIME(7,0,0),"3",IF(A2<TIME(15,0,0),1,IF(A 2<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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

If Between Dates Function - please help
 

Thanks Rick. This worked very well.
-Misty

Rick Rothstein (MVP - VB) wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1

If Between Dates Function - please help
 

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

"Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote in message
news:73b36f7a2c5fe@uwe...
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

Bob Phillips wrote:
="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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200706/1