Shiftless Worksheet - fill shift cell based on time cell

[joetech71]

I have created a spreadsheet for collecting data across all two work shifts at my company. The workers enter the date and time into two columns and their shift (1 or 3)in another column. I would like to fill the "shift" field based on input in "time" field.


The time field is formatted hh:mm using a 24 hour clock. we are currently running two shifts:


Shift 1 - 7:01 to 15:30,

shift 2 - 22:01 to 7:00

 

[joetech71]

This is the formula I tried:


=IF(AND(C832<=17:00,C832>=7:01),"1","3")

 

[Swaroop]

Hey joetech71,


Donno if I got it the DUMB way - but here it goes:


=IF(AND((HOUR(C832)+MIN(C832))>=7.292361,(HOUR(C832)+MIN(C832))<=15.649),"1",IF(OR((HOUR(C832)+MIN(C832))>=22.91,(HOUR(C832)+MIN(C832))<=7.291667),"3",""))

 

[joetech71]

Thank you very much Swaroop; it is indeed ugly - but it works.