How to compensate for a second shift spread of data across two dates

Hi,

I have a spreadsheet that totals data for two different shifts. On my "TOTALS" sheet it pulls the data from the "DATA ENTRY" sheet that matches criteria. I used Today() to pull the totals for each day. The problem is that second shift goes until about 3-4 AM and the Today() function won't show all the second shift data since the date rolls over. I compensate for this (in my real spreadsheet) by forcing it to use the previous days date if the time is between midnight and 5AM.

My issue now is how to make the "TOTALS" data for 2nd shift see today's date totals as well as the next early mornings data from midnight until about 5AM. Also, it can only perform this if it's between midnight and 5 AM since it will always grab the previous mornings (midnight to 5am) otherwise.

Thoughts on how to achieve this? I'm pretty good with VBA if that's a better way.

Thank you for your time and assistance.

SAMPLE WORKSHEET: Machine Data - 7-02-15.xlsb

Jay

Perhaps something like this, using array formulas...


regards

Thanks for the suggestion azumi, I ended up using a time check statement to see if it was between 12 midnight and 5 AM and if it was, subtract 1 day from the date being entered. This way the 2nd shift data will all be on one date instead of across two days. It works perfectly.

The basic code is:

Please Login or Register  to view this content.