pulling a rabbit out of a hat (or converting timestamp to a shift)

[Lawrence Dodge]

Hi all--

I have a spreadsheet that makes a time stamp when a cell is filled. The time stamp is in the form MM/DD/YY HH:mm (with 24 hour time). In the next column I need to be able to say if the time is between 07:30 - 15:30 then a 1 is displayed (for 1st shift), if between 15:31 -- 23:30 then a 2 is displayed (for 2nd shift) and then if between 23:31 -- 7:30 then a 3 (for 3rd shift). I am NO good with VBA, so if someone could help me with the "=IF......" statement, I would be much obliged.

Thanks in advance

LCD

 

[vaskov17]

Try this:


=IF(MOD(A1,1)<VALUE("7:30"),3,IF(MOD(A1,1)<VALUE("15:30"),1,IF(MOD(A1,1)<VALUE("23:30"),2,3)))


that assumes the time stamp is in A1

 

[Lawrence Dodge]

You are AWESOME, thank you thank you thank you