Setting condition when current time falls betwen two times

[shahdelsol]

I am trying to set up an IF formula when the current time falls between 21:00 and 23:59. I have used this formula but it is not working. If the current time is between 21:00 and 23:59, I want to get tomorrow's date but if it is passed midnight, I want to get today's date. Can anyone help?

=IF(AND(NOW()>="21:00",NOW()<="23:59"),TODAY()+1,TODAY())

 

[CWatts]

It's because you're using text rather than time and I think any success would have been coincidental. Here's an alternative:

=TODAY()+IF(HOUR(NOW())>=21,1,0)

For yours to have worked, you'd have to had used TIMEVALUE("21:00") instead of just "21:00"

 

[JoeMo]

Here's an alternative:

=IF(AND(MOD(NOW(),1)>=TIMEVALUE("21:00"),MOD(NOW(),1)<=TIMEVALUE("23:59")),TODAY()+1,TODAY())

 

[shahdelsol]

Thank you for quick response. I will try it tonight.

It's because you're using text rather than time and I think any success would have been coincidental. Here's an alternative:

=TODAY()+IF(HOUR(NOW())>=21,1,0)

For yours to have worked, you'd have to had used TIMEVALUE("21:00") instead of just "21:00"

 

[shahdelsol]

Thanks to you as well. I will try yours too. I like to keep both formulas.

Here's an alternative:

=IF(AND(MOD(NOW(),1)>=TIMEVALUE("21:00"),MOD(NOW(),1)<=TIMEVALUE("23:59")),TODAY()+1,TODAY())

 

[shahdelsol]

Thank to you both. It worked very well!