Operational days

[CR4Reid]

Hi there,

I've got a unique problem calculating data based on an operational day. I've got buses that comes in at 4 am and required to be fueled starting at 6 am. The operational day is from 4 am today til tomorrow 3:59:59. For example: today is Thursday and the time is 7:00 am. Bus 3359 is fueled. This is a Thursday transaction. Bus 6419 was fueled Friday 2:30 am; this is a Thursday transaction. Bus 7991 is fueled at 5:30 am Friday morning; this is a Friday transaction.

My report is structured as followed. Left side of the report shows all fueling activities for the current day. If I print the report at 7:00 am Thursday, it should show all activities from 4:00 am Thursday through 3:59:59 Friday morning. At 4:00 AM Friday, all activities will be transferred to the right side of the report (Yesterday's activities) and essentially, all of yesterday's activities will be on the right side. The left side will show all buses that's being fueled today, which means Friday 4:00 am through Saturday 3:59:59 am.

I can't seem to wrap my head around the formula that will do this in my report; ie. moving all of "Today's" data to "Yesterday" as at 4:00 am and move them back to "Today" as they are fueled in the period. Sample data is as followed:

Print Time: Thursday 9:00 am (period Thursday 4:00 am - Friday 3:59:59 am)
Today:

​

​

​

Yesterday

Bus 3359 - 300 litres Thursday March 20, 6:00 am
Bus 3360 - 259 litres Thursday March 20, 6:21 am
Bus 3379 - 100 litres Thursday March 20, 9:00 am

​

​

​

Bus 7759 - 50 litres Wednesday March 19, 9:00 pm

​

​

​

Bus 9819 - 120 litres Thursday March 20, 2:30 am


Print Time: Friday 7:05 am (period Friday 4:00 am - Saturday 3:59:59 am)
Today:

​

​

​

Yesterday
Bus 2315 - 200 litres Friday March 21, 6:03 am
Bus 2316 - 100 litres Friday March 21, 7:00 am

​

​

​

​

Bus 3359 - 300 litres Thursday March 20, 6:00 am

​

​

​

​

Bus 3360 - 259 litres Thursday March 20, 6:21 am

​

​

​

​

Bus 3379 - 100 litres Thursday March 20, 9:00 am


Print time: Friday 9:30 pm (period Friday 4:00 am - Saturday 3:59:59 am)
Today:

​

​

​

Yesterday
Bus 2315 - 200 litres Friday March 21, 6:03 am
Bus 2316 - 100 litres Friday March 21, 7:00 am
Bus 3359 - 150 litres Friday March 21, 6:21 am
Bus 3360 - 200 litres Friday March 21, 7:30 am
Bus 3379 - 60 litres Friday March 21, 8:00 am
Bus 7759 - 100 litres Friday March 21, 7:30 pm
Bus 9819 - 50 litres Friday March 21, 7:31 pm

Note: there are no activities under "Yesterday" because all buses have been fueled at time of printing for the period "Today".

I have other fields like KM's but I only need to move time of fueling and amount of fuel to move from one side to the other as the dates roll.

Any help is appreciated - thank you.

Reid

​

 

[SkipVought]

Hi,

Subtract 4hrs (1/6 of a day) from each date for your op calculations.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}