Why can't I get 'NOW() > CELL' to work?

[eli_m]

Hi,
The formula in D1 always shows up as "No" even though NOW() is past the 22/11/2022:
=IF(NOW()>C1,"Yes","No")

A1 = 22-November-2022

B1 = 09:30am

C1 Formula = TEXT(A1,"dd/mm/yyyy ")&TEXT(B1,"hh:mm")
C1 = 22/11/2022 9:30am

NOW() = 22/11/2022 11:33

I tried removing the am/pm from C1 but still it has the same result.

Is there a better way to do this?

Thanks in advance!

 

[Habtest]

Try change C1 formula to A1+B1, and format C1 to "dd/mm/yyyy hh:mm"

 

[eli_m]

Try change C1 formula to A1+B1, and format C1 to "dd/mm/yyyy hh:mm"

I get a #VALUE! error when I do A1+B1:

 

[Micron]

Now is date/time data type. You can't compare it to text.
Both need to be date/time data

 

[JoeMo]

Your C1 formula returns text not a number. Now needs to be compared to a date serial number like below where A1 is formatted as "d-mmmm-yyyy".

Book1
	A	B	C	D
1	20-November-2022	9:30 AM		Yes
Sheet2

Cell Formulas
Range	Formula
D1	D1	=IF(NOW()>A1+B1,"Yes","No")

 

[Micron]

You also used + and not &. That makes all the difference.

 

[Peter_SSs]

C1 Formula = TEXT(A1,"dd/mm/yyyy ")&TEXT(B1,"hh:mm")
C1 = 22/11/2022 9:30am

I get a #VALUE! error when I do A1+B1:

Both of the above indicate that your time in B1 is text and not an actual time (number). For example, if it was an actual time then your value in C1 would have been
22/11/2022 09:30
Try this in C1 instead and format C1 with your desired date/time format.

22 11 22.xlsm
	A	B	C	D
1	22/11/2022	9:30am	22/11/2022 09:30 AM	Yes
NOW

Cell Formulas
Range	Formula
C1	C1	=A1+TIMEVALUE(REPLACE(B1,SEARCH("m",B1)-1,0," "))
D1	D1	=IF(NOW()>C1,"Yes","No")

 

[eli_m]

Both of the above indicate that your time in B1 is text and not an actual time (number). For example, if it was an actual time then your value in C1 would have been
22/11/2022 09:30
Try this in C1 instead and format C1 with your desired date/time format.

22 11 22.xlsm
	A	B	C	D
1	22/11/2022	9:30am	22/11/2022 09:30 AM	Yes
NOW

Cell Formulas
Range	Formula
C1	C1	=A1+TIMEVALUE(REPLACE(B1,SEARCH("m",B1)-1,0," "))
D1	D1	=IF(NOW()>C1,"Yes","No")

=A1+TIMEVALUE(REPLACE(B1,SEARCH("m",B1)-1,0," "))

worked great!
thank you

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.