Assign a Value if Time field is populated

[vixenmyrtle]

Hi,
very basic question i'm sorry. but will need help for a simple formula to return a value if filed has time data on it. See table below.

Time In	TIME IN
<Blank>	No
05:00 AM	Yes

 

[Fluff]

How about

=IF(ISNUMBER(A2),"Yes","No")

 

[Siddharth Rout]

You can use this if you want?

=IFERROR(IF(A1="","NO",IF(TIMEVALUE(TEXT(A1,"hh:mm:ss"))=A1, "YES", "NO")),"NO")

If the cell is blank or has invalid value, it will show NO. If it is a valid time then it will show YES.

 

[vixenmyrtle]

=IFERROR(IF(A1="","NO",IF(TIMEVALUE(TEXT(A1,"hh:mm:ss"))=A1, "YES", "NO")),"NO")

thank you will try this

 

[Peter_SSs]

It depends on what might be in your column. If it is only time or blank or text then the given suggestions may well suit you. However, if other numerical data/formats might be in the column then they may return incorrect results - see samples below.
In column D I have suggested another option which picks up on those possible errors. Note though that CELL() is a volatile function so could impact your sheet performance if there is a very large number of these formulas.

vixenmyrtle.xlsm
	A	B	C	D
1	Time In	Post #2	Post #3	Post #5
2		No	NO	No
3	5:00 AM	Yes	YES	Yes
4	$0.36	Yes	YES	No
5	0.2	Yes	YES	No
6	Text	No	NO	No
7	20%	Yes	YES	No
8	1/08/2021	Yes	NO	No
9	12:33:15	Yes	YES	Yes
10	12:00	Yes	YES	Yes
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IF(ISNUMBER(A2),"Yes","No")
C2:C10	C2	=IFERROR(IF(A2="","NO",IF(TIMEVALUE(TEXT(A2,"hh:mm:ss"))=A2, "YES", "NO")),"NO")
D2:D10	D2	=IF(AND(A2<>"",OR(CELL("format",A2)={"D6","D7","D8","D9"})),"Yes","No")

 

[Siddharth Rout]

Peter has raised a very important point.

vixenmyrtle, the formats can return unexpected results (Visually) with what you want. You need to undertsand how Excel stores time.

0 is 00:00:00, 0.25 is 6:00:00, 0.50 is 12:00:00, 1 is 23:59:00 (See the image below)

The underlying value of $0.36 is 0.36 which is a valid time and hence my formula gives a YES. It will not give a YES for $7.5 or for NOW() which is formatted as time.