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