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

eli_m

Board Regular
Joined
Jun 2, 2022
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try change C1 formula to A1+B1, and format C1 to "dd/mm/yyyy hh:mm"
 
Upvote 0
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:
1669078332939.png
 
Upvote 0
Now is date/time data type. You can't compare it to text.
Both need to be date/time data
 
Upvote 0
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
ABCD
120-November-20229:30 AMYes
Sheet2
Cell Formulas
RangeFormula
D1D1=IF(NOW()>A1+B1,"Yes","No")
 
Upvote 0
You also used + and not &. That makes all the difference.
 
Upvote 0
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:
1669078332939.png
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
ABCD
122/11/20229:30am22/11/2022 09:30 AMYes
NOW
Cell Formulas
RangeFormula
C1C1=A1+TIMEVALUE(REPLACE(B1,SEARCH("m",B1)-1,0," "))
D1D1=IF(NOW()>C1,"Yes","No")
 
Upvote 0
Solution
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
ABCD
122/11/20229:30am22/11/2022 09:30 AMYes
NOW
Cell Formulas
RangeFormula
C1C1=A1+TIMEVALUE(REPLACE(B1,SEARCH("m",B1)-1,0," "))
D1D1=IF(NOW()>C1,"Yes","No")

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

worked great!
thank you :)
 
Upvote 0
You're welcome. Thanks for the confirmation. :)
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top