|
Need absolute time from yesterday
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 |
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? -- HTH Bob Phillips "MIke K" wrote in message ... 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 |
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 -----Original Message----- 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? -- HTH Bob Phillips "MIke K" wrote in message ... 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 . |
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 -- HTH Bob Phillips "Mike K" wrote in message ... 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 -----Original Message----- 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? -- HTH Bob Phillips "MIke K" wrote in message ... 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 . |
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")? In article , "Bob Phillips" wrote: 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 |
| All times are GMT +1. The time now is 05:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com