Check if time falls between two times?

[Simon Lloyd]

Hi all
I'm having a little trouble with this formula: (D1 is formatted =Now()
=IF(OR(D1>TIMEVALUE("07:00"),D1<TIMEVALUE("19:00")),"Days","Nights"
i also tried
=IF(OR(TEXT(D2,"hh:mm")>"07:00",TEXT(D2,"hh:mm")<"19:00"),"Days","Nights"
An
=IF(OR(D1>--"07:00",D1<--"19:00"),"Days","Nights"
But the result is always "Days", all i'm looking to check is if th
time now (time of opening the workbook or activating a sheet but that
not important) is in between 7am and 7pm, if it is display the wor
"Days" if not "Nights

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Roger Govier]

Hi Simon

I think you need to AND the conditions, not OR

Also, take the MOD(D1) to just get the time element, otherwise with the day
value of 40000+ in front of the decimal time, it is always going to fail

=IF(AND(MOD(D1,1)>TIMEVALUE("07:00"),MOD(D1,1)<TIMEVALUE("19:00")),"Days","Nights")

--
Regards
Roger Govier

Hi all,
I'm having a little trouble with this formula: (D1 is formatted =Now())
=IF(OR(D1>TIMEVALUE("07:00"),D1<TIMEVALUE("19:00")),"Days","Nights")
i also tried:
=IF(OR(TEXT(D2,"hh:mm")>"07:00",TEXT(D2,"hh:mm")<"19:00"),"Days","Nights")
And
=IF(OR(D1>--"07:00",D1<--"19:00"),"Days","Nights")
But the result is always "Days", all i'm looking to check is if the
time now (time of opening the workbook or activating a sheet but thats
not important) is in between 7am and 7pm, if it is display the word
"Days" if not "Nights"


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=146366


__________ Information from ESET Smart Security, version of virus
signature database 4528 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4528 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Mike H]

Simon,

you were so close

=IF(AND(D1>TIMEVALUE("07:00"),D1<TIMEVALUE("19:00")),"Days","Nights")

And instead of OR

Mike

 

[Simon Lloyd]

Thanks both, i'm probably going to use the MOD too and go with havin
Now() in the formula itself as i don't really want to be tied t
updating or refreshing a cell

Again thanks

Simon

you were so clos

=IF(AND(D1>TIMEVALUE("07:00"),D1<TIMEVALUE("19:00")),"Days","Nights"

And instead of O

Mik




Microsoft Office Discussion' (http://www.thecodecage.com)

Hi Simo

I think you need to AND the conditions, not O

Also, take the MOD(D1) to just get the time element, otherwise with th
da
value of 40000+ in front of the decimal time, it is always going t
fai

=IF(AND(MOD(D1,1)>TIMEVALUE("07:00"),MOD(D1,1)<TIMEVALUE("19:00")),"Days","Nights"

-
Regard
Roger Govie



Microsoft Office Discussion' (http://www.thecodecage.com)

-----------------------------------------------------------------------


(http://www.eset.com




__________ Information from ESET Smart Security, version of viru
signature database 4528 (20091021) _________

The message was checked by ESET Smart Security

'ESET - Antivirus Software with Spyware and Malware Protection
(http://www.eset.com

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Bob Phillips]

Try

=IF(AND(MOD(D1,1)>--"07:00:00",MOD(D1,1)<--"19:00:00"),"Days","Nights")

 

[Simon Lloyd]

Thanks Bob! :

Tr

=IF(AND(MOD(D1,1)>--"07:00:00",MOD(D1,1)<--"19:00:00"),"Days","Nights"

-
_________________________________
HT

Bo



Microsoft Office Discussion' (http://www.thecodecage.com)

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com