IF and NOW() together in formula

[liquid25]

I want a formula to return zero if the current time is after 6PM and 1 if not. Is there a formula that can acheive this?

Cheers

 

[nbrcrunch]

So from zero-hundred hours to 1800 is value 1 and
from 1801 to 2400 is value zero?

If A1 houses your time, then:

=IF(TIME(HOUR(A1),MINUTE(A1),0)>0.75,0,1)

Useful Reading: (Scroll down to Date & Time Serials)
http://www.mvps.org/dmcritchie/excel/datetime.htm

 

[Smitty]

How about:

=IF(A1>0.75,0,1)

Hope that helps,

Smitty

 

[liquid25]

cheers, that's great.

in what format does the time need to be in cell A1?

can it be the function NOW()?

 

[Smitty]

ADVERTISEMENT

can it be the function NOW()?

That should work just fine.

Smitty

 

[nbrcrunch]

please try it before asking. it is so much easier to try something like this than wait for answer.

 

[Nimrod]

ADVERTISEMENT

I tried PennySavers solution with a now() and it worked fine !

=IF(NOW()>0.75,0,1)

 

[Brian from Maui]

please try it before asking. it is so much easier to try something like this than wait for answer.

Why not?

Edit,

Where's the other remark you made?

 

[nbrcrunch]

Brian:

I deleted it. the first time I tried it, it didn't work (for some unknown reason). But when I saw the 2nd post, I went back and tried it again.

Every so often either it must be my install of Excel or (more likely) I typed something different than what I thought I typed (as a formula). Sometimes I will try so many variations of a formula that I can't remember what I have & haven't tried. Either during my sleep or fresh the next day, it comes to me what I need to do.

 

[Brian from Maui]

Brian:

I deleted it. the first time I tried it, it didn't work (for some unknown reason). But when I saw the 2nd post, I went back and tried it again.

Every so often either it must be my install of Excel or (more likely) I typed something different than what I thought I typed (as a formula). Sometimes I will try so many variations of a formula that I can't remember what I have & haven't tried. Either during my sleep or fresh the next day, it comes to me what I need to do.

Not a problem....

Happens to me ALL the time!