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:

[TABLE="width: 341"]
<tbody>[TR]
[TD]Shift[/TD]
[TD]Start Time[/TD]
[TD] End Time[/TD]
[/TR]
[TR]
[TD]First[/TD]
[TD="align: right"]6:21:00[/TD]
[TD="align: right"]14:20:00[/TD]
[/TR]
[TR]
[TD]Second[/TD]
[TD="align: right"]14:21:00[/TD]
[TD="align: right"]22:20:00[/TD]
[/TR]
[TR]
[TD]Third[/TD]
[TD="align: right"]22:21:00[/TD]
[TD="align: right"]6:20:00[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 253"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD="align: right"]0:00:00 [/TD]
[TD]Third[/TD]
[/TR]
[TR]
[TD="align: right"]6:21:00 [/TD]
[TD]First[/TD]
[/TR]
[TR]
[TD="align: right"]14:21:00[/TD]
[TD] Second[/TD]
[/TR]
[TR]
[TD="align: right"]22:21:00[/TD]
[TD] Third[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 253"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD="align: right"]0:00:00[/TD]
[TD]Third[/TD]
[/TR]
[TR]
[TD="align: right"]6:21:00[/TD]
[TD]First[/TD]
[/TR]
[TR]
[TD="align: right"]14:21:00[/TD]
[TD] Second[/TD]
[/TR]
[TR]
[TD="align: right"]22:21:00[/TD]
[TD] Third[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 253"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD="align: right"]0:00:00[/TD]
[TD]Third[/TD]
[/TR]
[TR]
[TD="align: right"]6:21:00[/TD]
[TD]First[/TD]
[/TR]
[TR]
[TD="align: right"]14:21:00[/TD]
[TD] Second[/TD]
[/TR]
[TR]
[TD="align: right"]22:21:00[/TD]
[TD] Third[/TD]
[/TR]
</tbody>[/TABLE]

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