Check if time falls between two times?
in [Functions]
|
From: Simon Lloyd on 21 Oct 2009 07:55 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
From: Mike H on 21 Oct 2009 09:01 Simon, you were so close =IF(AND(D1>TIMEVALUE("07:00"),D1<TIMEVALUE("19:00")),"Days","Nights") And instead of OR Mike "Simon Lloyd" wrote: > > 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 > > . >
From: Roger Govier on 21 Oct 2009 08:59 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 "Simon Lloyd" <Simon.Lloyd.40enji(a)thecodecage.com> wrote in message news:Simon.Lloyd.40enji(a)thecodecage.com... > > 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
From: Bob Phillips on 21 Oct 2009 09:13 Try =IF(AND(MOD(D1,1)>--"07:00:00",MOD(D1,1)<--"19:00:00"),"Days","Nights") -- __________________________________ HTH Bob "Simon Lloyd" <Simon.Lloyd.40enji(a)thecodecage.com> wrote in message news:Simon.Lloyd.40enji(a)thecodecage.com... > > 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 >
From: Simon Lloyd on 21 Oct 2009 09:12 Thanks both, i'm probably going to use the MOD too and go with having Now() in the formula itself as i don't really want to be tied to updating or refreshing a cell. Again thanks. Mike H;532644 Wrote: > Simon, > > you were so close > > =IF(AND(D1>TIMEVALUE("07:00"),D1<TIMEVALUE("19:00")),"Days","Nights") > > And instead of OR > > Mike > > "Simon Lloyd" wrote: > > > > > 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' ('The Code Cage - Microsoft Office Help - > Microsoft Office Discussion' (http://www.thecodecage.com)) > > > ------------------------------------------------------------------------ > > Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon > Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) > > View this thread: 'Check if time falls between two times? - The Code > Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=146366) > > > > . > > Roger Govier;532645 Wrote: > 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 > > "Simon Lloyd" <Simon.Lloyd.40enji(a)thecodecage.com> wrote in message > news:Simon.Lloyd.40enji(a)thecodecage.com... > > > > 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' ('The Code Cage - Microsoft Office Help - > Microsoft Office Discussion' (http://www.thecodecage.com)) > > > ------------------------------------------------------------------------ > > Simon Lloyd's Profile: > > 'The Code Cage Forums - View Profile: Simon Lloyd' > (http://www.thecodecage.com/forumz/member.php?userid=1) > > View this thread: > > 'Check if time falls between two times? - The Code Cage Forums' > (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. > > > > 'ESET - Antivirus Software with Spyware and Malware Protection' > (http://www.eset.com) > > > > > > > > __________ Information from ESET Smart Security, version of virus > 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 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
|
Next
|
Last
Pages: 1 2 Prev: Increment invoice number Next: how can I use randbetween without repeating numbers in a set |