Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
time sheet | Excel Worksheet Functions | |||
Round Time | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |