Need absolute time from yesterday

[MIke K]

Oh wise ones,
I run a report in the morning which calls
cells from yesterday morning at 7:00AM until now- if now
is before 7:00AM. Or if now is past 7:00AM, from yesterday
at 7:00AM until today at 7:00AM. I can do NOW and NOW-1
but I can't figure out the 7:00AM absolute time. Help is
much appreciated.

Mike

 

[Bob Phillips]

Mike,

If I understand correctly, --"07:00:00" can be used to test fro 7:00AM. E,g

=IF(A1>--"07:00:00", etc.

Does that help?

 

[Mike K]

Hi Bob,
Just got back in the office. The formula below
is valid, but it returns everything as true. I need to
integrate the date with the time to work properly. Today
is 1-21-05 and it is 7:51AM. I need it to return a true
for all cells from 1-20-05 7:00AM to 1-21-05 7:00AM
preferably without explicitly putting in the date/time.
How do I work the date part into the format?

Mike

 

[Bob Phillips]

Mike,

Can you adapt this to what you want

=IF(OR(AND(INT(A1)=TODAY()-1,A1-INT(A1)>=--"07:00:00"),AND(INT(A1)=TODAY(),A
1-INT(A1)<"--7:00;00")),TRUE,FALSE)

basically it tests A1 to be either
yesterday and time 7:00AM or later OR
today and time earlier than 7:00AM

It assumes A1 has date and time

 

[JE McGimpsey]

I may be jumping in late, but wouldn't

=INT(A1-7/24)=(TODAY()-1)

do the same thing (after correcting the "--7:00;00" to --"7:00:00")?