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.

 

[Dscalf1]

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

It comes out as TRUE

 

[mole999]

are the columns used the same as the formula above from Aryatect

 

[Dscalf1]

are the columns used the same as the formula above from Aryatect

No my columns are as follows:

The time stamp is in cell: BH6

My chart that looks like this Starts in cell CN4 :

CN CO CP
4 Shift Start Time End Time
5 First 6:21:00 14:20:00
6 Second 14:21:00 22:20:00
7 Third 22:21:00 6:20:00


the word first second or third should go in cell BI6

 

[mole999]

just a thought but i think the date needs to be dropped i the formula otherwise they will never work

 

[Dscalf1]

just a thought but i think the date needs to be dropped i the formula otherwise they will never work

=IF(AND(BH6>=$CO$5,BH6<=$CP$5),$CN$5,IF(AND(BH6>=$CO$6,BH6<=$CP$6),$CN$6,IF(AND(BH6>=$CO$7,BH6<=0.99999),$CN$7,IF(AND(BH6>=0,BH6<=$CP$7),$CN$7))))

Okay just changed the format to just the time instead of date and time and this formula is still giving me a false instead of a shift name..

 

[mole999]

i was thinking more on the INDEX / MATCH formula but using this formula (adjusted to remove the date) > =A2-INT(A2) The date portion is a whole number so this just leaves the time value

Like This >

Excel Workbook
	A	B	C	D	E
1	Time	Shift			17:25
2	00:00:00	Third
3	06:21:00	First
4	14:21:00	Second
5	22:21:00	Third
6					Second
7
8	CP	CN
9	col	col
Sheet1

 

[mole999]

using your stated locations >

Excel Workbook
	BH	BI	BJ	CM	CN	CO	CP
2
3					Shift		Time
4					Third		00:00:00
5					First		06:21:00
6	17:25	Second			Second		14:21:00
7					Third		22:21:00
Sheet1

 

[Dscalf1]

i was thinking more on the INDEX / MATCH formula but using this formula (adjusted to remove the date) > =A2-INT(A2) The date portion is a whole number so this just leaves the time value

Like This > Sheet1

	A	B	C	D	E
Time	Shift
Third
First
Second
Third
				Second
CP	CN
col	col

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:73px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]17:25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]00:00:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]06:21:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]14:21:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]22:21:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E6 =IFERROR(INDEX($B$2:$B$5,MATCH(E1-INT(E1),$A$2:$A$5,1)),"ERROR") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This worked!! Thank you so much!!

 

[mole999]

just a matter of getting the right info from the right places The updated version should fit with your data