24hr period across two days.

[dmic23]

Hi All,


I'm looking for a formula for a daily excel report to only show a 24hr period between 05:00 and 04:59 the next day. The way the report is generated, will only pull a 24hr period from 0:00-24:00 for the same day so I need to pull two days. All times are in column E and between the two days there are usually around 500 rows. I need to remove all the rows that are <05:00 on day 1 and >04:59 on day 2.


There is probably a simple solution although haven't found one yet. I'm happy to explain in greater detail if needed. Any help is greatly appreciated.

 

[Luke M]

Not exactly sure how you are pulling the data, but the criteria fields could be generated via:

=TODAY()-1+TIME(5,0,0)

and

=TODAY()+TIME(4,59,0)


as the lower and upper limits. If your report is being generated via an Advanced Filter or a Microsoft Query with conditions, this method should work.

 

[Montrey]

=time() is a new formula for me. Kewl

 

[dmic23]

Luke,

Thank you for the reply, although can't seem to get your formula to work. It returns the current date at 5am.


I'm not sure how the report is generated as it comes from another system that we use to track our work.


The format for column E is date/time: mm/dd/yyyy 00:00:00 am/pm.

 

[Luke M]

What exactly do you want to do with the data then once it's in your workbook? We can filter it in place or copy to a new location (both would use Advanced Filter)

http://www.contextures.com/xladvfilter01.html

For both cases, you would setup your criteria cells with the Name of header from col E, and the two formulas listed below to setup the criteria values:

=">="&TODAY()-1+TIME(5,0,0)

="<="&TODAY()+TIME(4,59,0)


Or, we can try to get fancy and use a macro to actually delete the rows. In my opinion, this would be slightly less preferred as it deletes data.