Need If Formula

[faraz502]

I have a sheet in this sheet I need a formula I used the below-mentioned formula but gives me always the first condition.

Date	Time	Shift
7/11/2023 13:20	13	day
7/11/2023 1:20	01	day
7/11/2023 2:20	02	day

Currently used Below mentioned Formula.

Date	Time	Shift
7/11/2023 13:20	13	=IF(HOUR(AD3)<=10,"day","night")

 

[Kevin_Li]

Are you sure your data is in cell AD3?

 

[awoohaw]

it looks like the Time column is formatted as Text, not numbers.
and is AD column the left column or the middle column?

Using the xl2bb add in can help the forum help you. Link below.

 

[faraz502]

Are you sure your data is in cell AD3?

YES

 

[awoohaw]

i think your cell reference is incorrect, it works for me:

Book1
	AD	AE	AF	AG	AH
1	Date	Time	Shift
2	2023-07-11 13:20	13.00	night	=HOUR(AD2)	=IF(HOUR(AD2)<=10,"day","night")
3	2023-07-11 01:20	1.00	day	=HOUR(AD3)	=IF(HOUR(AD3)<=10,"day","night")
4	2023-07-11 02:20	2.00	day	=HOUR(AD4)	=IF(HOUR(AD4)<=10,"day","night")
faraz502

Cell Formulas
Range	Formula
AE2:AE4	AE2	=HOUR(AD2)
AF2:AF4	AF2	=IF(HOUR(AD2)<=10,"day","night")
AG2:AH4	AG2	=FORMULATEXT(AE2)

​

​

 

[awoohaw]

YES

please verify that the word date is in cell AD1 or AD2. If it is in AD1 your cell reference is wrong.

 

[Fluff]

Assuming that AD3 is the column labelled time, then you just need

=IF(AD3<=10,"day","night")