I'm working on a somewhat complex function trying to have excel make a choice
based on current time. Here is the statement I'm trying to use:
=IF(AND(NOW()A17,NOW()<A18),(IF(HLOOKUP(TODAY()-1,$B$1:$T$32,ROW($A$3))="","P",HLOOKUP(TODAY()-1,$B$1:$T$32,ROW($A$3)))),(IF(HLOOKUP(TODAY(),$B$1 :$T$32,ROW($A$3))="","P",HLOOKUP(TODAY(),$B$1:$T$3 2,ROW($A$3)))))
A17 and A18 will contain time values to create a "window of time" where I
want the current time to either fall within or outside of. Based on this,
the function should make a choice on where to look up data.
I'm creating a scheduling sheet for work and we work rotating shift
schedules. The problem is that if we work a mid shift the current time will
move from the current day to the next but I need the look up to use the
current day (when we start work) and not the one after midnight for the
attendance log. The HLOOKUP function is used to populate a column with the
current days scheduled attendance) If I use static values instead of the
NOW() function the statement seems to work but I need to use NOW() to
correctly interpret the current day/time (I think). Any ideas how I can
accomplish this using the NOW() function??
Thanks,
Bill

"Bill" wrote...
I'm working on a somewhat complex function trying to have excel
make a choice based on current time. Here is the statement I'm
trying to use:

=IF(AND(NOW()A17,NOW()<A18),
(IF(HLOOKUP(TODAY()-1,$B$1:$T$32,ROW($A$3))="","P",
HLOOKUP(TODAY()-1,$B$1:$T$32,ROW($A$3)))),
(IF(HLOOKUP(TODAY(),$B$1:$T$32,ROW($A$3))="","P ",
HLOOKUP(TODAY(),$B$1:$T$32,ROW($A$3)))))

A17 and A18 will contain time values to create a "window of time"
where I want the current time to either fall within or outside of.
....
. . . If I use static values instead of the
NOW() function the statement seems to work but I need to use
NOW() to correctly interpret the current day/time (I think). Any
ideas how I can accomplish this using the NOW() function??

What do the static values look like? What do A17 and A18 look like?

NOW() returns date and time, so if the A17 and A18 values are only times,
then NOW() will always be greater than the larger of them (so never between
them).

That said, looks like you could simplify your formula to

=IF(HLOOKUP(TODAY()-AND(NOW()A17,NOW()<A18),$B$1:$T$32,ROW($A$3))="",
"P",HLOOKUP(TODAY()-AND(NOW()A17,NOW()<A18),$B$1:$T$32,ROW($A$3)))

I tried the suggestion you posted but it didn't work. Here's the idea... I
have dates on the top row of my sheet and based on today's date it scans the
top row and then returns the projectred attendance status for an individual
on the corresponding column to today's date. The problem is that if we work
over night the sheet will automatically switch the projected attendance
status from the actual day we came to work to the following day at midnight.
So, someone may have been on vacation the day we came in to work but will
return to work the following night so when the clock turns to midnight the
actual status is showing they've returned when in actuality they won't be in
until the following evening. What I'm trying to do is have the sheet
determine if the current time (NOW()) is between 23:59 and 06:00 so that if
that's the case, excel will disregard the date rollover and stay on the
current day until we come into work the following night. When our shift is
over I save a copy to a different folder and then make changes as necessary
for the following day. This give management a snapshot in time when they
view the sheet. Maybe there's a better way to accomplish this but this is
what I have to work with.
Thanks,
Bill

"Harlan Grove" wrote:

"Bill" wrote...
I'm working on a somewhat complex function trying to have excel
make a choice based on current time. Here is the statement I'm
trying to use:

=IF(AND(NOW()A17,NOW()<A18),
(IF(HLOOKUP(TODAY()-1,$B$1:$T$32,ROW($A$3))="","P",
HLOOKUP(TODAY()-1,$B$1:$T$32,ROW($A$3)))),
(IF(HLOOKUP(TODAY(),$B$1:$T$32,ROW($A$3))="","P ",
HLOOKUP(TODAY(),$B$1:$T$32,ROW($A$3)))))

A17 and A18 will contain time values to create a "window of time"
where I want the current time to either fall within or outside of.
....
. . . If I use static values instead of the
NOW() function the statement seems to work but I need to use
NOW() to correctly interpret the current day/time (I think). Any
ideas how I can accomplish this using the NOW() function??

What do the static values look like? What do A17 and A18 look like?

NOW() returns date and time, so if the A17 and A18 values are only times,
then NOW() will always be greater than the larger of them (so never between
them).

That said, looks like you could simplify your formula to

=IF(HLOOKUP(TODAY()-AND(NOW()A17,NOW()<A18),$B$1:$T$32,ROW($A$3))="",
"P",HLOOKUP(TODAY()-AND(NOW()A17,NOW()<A18),$B$1:$T$32,ROW($A$3)))

Bill wrote...
....
. . . What I'm trying to do is have the sheet
determine if the current time (NOW()) is between 23:59 and 06:00 so
that if
....

I'd guess this is your mistake. NOW() doesn't return *JUST* the time.
It also returns the date. For example, if TODAY() returns 38460 (18
April 2005 in the 1900 date system), and the system clock shows the
time of day as 13:30, NOW() would return 38460.5625, *NOT* 0.5625. If
you want only the time of day, use either NOW()-TODAY() or
MOD(NOW(),1).

Also, there is no between 23:59 and 06:00 unless you mean Day 0 at
23:59 and Day 1 at 06:00, i.e., 0.999305555555556 and 1.25 as numbers,
respectively. If the 06:00 time value is just that, so equal to 0.25 as
a number (1/4 of the way through the day), then you mean that the time
value you're checking, T, must satisfy

T = 23:59 *OR* T <= 06:00

so you'd need to use

OR(T=TIME(23,59,00),T<=TIME(6,0,0))

That's OR(), not AND().