Shift name based on timestamp

[Dscalf1]

Hello I am trying to get a shift name based on a timestamp in a different cell. For example if my timestamp says "6/19/2019 13:41:37" I want the word "First" to show up in the next column to identify the shift name. I have searched and searched for an answer to this and cant seem to find it... The shift times are: First: 6:21-14:20, Second: 14:21-22:20 third: 22:21-6:20.

 

[mole999]

are there any other overlays that need to be considered, otherwise its a case of extracting the right hand time value and comparing to less than 1

 

[Dscalf1]

are there any other overlays that need to be considered, otherwise its a case of extracting the right hand time value and comparing to less than 1

Im not exactly sure what youre asking but essentially the times wouldnt overlap so for example.. 6:20:59 would be third shift and as soon as 6:21:00 hit it would be first shift

 

[Aryatect]

Hi Dscalf1,

I put all the Shift timings and shift in the excel starting with A1 shown below:

Shift	Start Time	End Time
First	6:21:00	14:20:00
Second	14:21:00	22:20:00
Third	22:21:00	6:20:00

<tbody>
</tbody>

and then put my time stamp in cell F2 and used below formula in G2

=IF(AND(F2>=$B$2,F2<=$C$2),$A$2,IF(AND(F2>=$B$3,F2<=$C$3),$A$3,IF(AND(F2>=$B$4,F2<=0.99999),$A$4,IF(AND(F2>=0,F2<=$C$4),$A$4))))

Let me know if it works for you.

Thanks

Hello I am trying to get a shift name based on a timestamp in a different cell. For example if my timestamp says "6/19/2019 13:41:37" I want the word "First" to show up in the next column to identify the shift name. I have searched and searched for an answer to this and cant seem to find it... The shift times are: First: 6:21-14:20, Second: 14:21-22:20 third: 22:21-6:20.

 

[Aryatect]

Another easier option would be:

putting the column starting with A1 again:

Time	Shift
0:00:00	Third
6:21:00	First
14:21:00	Second
22:21:00	Third

<colgroup><col><col></colgroup><tbody>
</tbody>

then putting time stamp in E2 and putting below formula in F2:

=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$5,1))

This should be a simple one.

Im not exactly sure what youre asking but essentially the times wouldnt overlap so for example.. 6:20:59 would be third shift and as soon as 6:21:00 hit it would be first shift

 

[Dscalf1]

Another easier option would be:

putting the column starting with A1 again:

Time	Shift
0:00:00	Third
6:21:00	First
14:21:00	Second
22:21:00	Third

<tbody>
</tbody>

then putting time stamp in E2 and putting below formula in F2:

=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$5,1))

This should be a simple one.

This one did not work for me it is showing an N/A but I think the first answer was going to work its just taking me a while to convert to the cells Im using! Ill let you know!

 

[Dscalf1]

Another easier option would be:

putting the column starting with A1 again:

Time	Shift
0:00:00	Third
6:21:00	First
14:21:00	Second
22:21:00	Third

<tbody>
</tbody>

then putting time stamp in E2 and putting below formula in F2:

=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$5,1))

This should be a simple one.

This returned false for me...

 

[mole999]

are you using actual time values? or text that looks like time?

 

[Dscalf1]

are you using actual time values? or text that looks like time?

Its an actual time value I believe. It is in the format "m/d/yyyy hh:mm:ss"

 

[mole999]

if you in another cell use = ISNUMBER(your cell date time value) and it returns as true then it is time, false will say it is text