Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function and DATES | Excel Worksheet Functions | |||
Function that compares dates | Excel Worksheet Functions | |||
IF function using dates ? | Excel Worksheet Functions | |||
IF function using a dates as the qualifier.. | Excel Worksheet Functions | |||
if function with dates | Excel Discussion (Misc queries) |