Networkdays Conundrum

[DubboPete]

Hello all,

I have a table which contains bookings for vehicles, based on the
start date/end date (date and time) per booking, and whether it spans
one day, two days or more.

I am trying to calculate the exact number of "networkdays" the vehicle
is used, but I have hit a brick wall because of the following
challenges.

Take ABC123 as the vehicle registration plate in question:

On Monday March1st it is booked out for three hours from 9am to 12
noon.
On Monday March 1st, it is then booked out from 1pm, on an extended
overnight trip to return Wednesday 3rd March at 2pm.
On Wednesday March 3rd, the vehicle is then booked out at 3pm to 5pm.

These bookings equal three separate entries in my table. In their
respective order, they are booked out for 1 networkday, and 3
networkdays, and 1 networkday, or that's how it appears in the table!

Although the bookings only span three actual networkdays (Monday,
Tuesday and Wednesday), the entries add up to 5 networkdays!

Anyone got any clues how I can work this out to three, instead of
five?

Fields in question would be :
Registration (text)
Actual_Start (Date/time, general format)
Actual_end (Date/time, general format)
Networkdays (number)

Oh, and the working hours are from 8am to 5pm per day Monday to
Friday, just in case that's needed!

Thanks in anticipation everyone

Pete

 

[NoodNutt]

G'day Pete

Have you considered calculating the hours to equal a decimal of the overall
9 hour day, by that I mean, rather than each hire (Networkdays) equalling
(1), it would calculate to 1.

Lets examine it this way:

Mon Mar 1: 09.00 - 12.00 = 3hrs = .33 NWD
Mon Mar 1: 13.00
Wed Mar 3: 14.00............= 18hrs = 2 NWD
Wed Mar 3: 15.00 - 17.00 = 2 hrs = .22 NWD

Would be heaps easier if it were a 10 hour day, then you could equate every
..1, either-way round (UP) any amount to the nearest full day and the new NWD
total = 3 Days.

HTH
Regards
Mark.

 

[Jeff Boyce]

I read over your post a couple times, but still didn't see where you defined
just what YOU mean by "networkdays".

Also, you mention a table with bookings, but I don't have a clear picture of
just what data/fields it contains.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[DubboPete]

G'day Pete

Have you considered calculating the hours to equal a decimal of the overall
9 hour day, by that I mean, rather than each hire (Networkdays) equalling
(1), it would calculate to 1.

Lets examine it this way:

Mon Mar 1: 09.00 - 12.00 = 3hrs = .33 NWD
Mon Mar 1: 13.00
Wed Mar 3: 14.00............= 18hrs = 2 NWD
Wed Mar 3: 15.00 - 17.00 = 2 hrs = .22 NWD

Would be heaps easier if it were a 10 hour day, then you could equate every
.1, either-way round (UP) any amount to the nearest full day and the new NWD
total = 3 Days.

HTH
Regards
Mark.

G'day Mark,

I thought about that one about six hours after my post, over the
course of a beer (of course), and came up with the same conclusion -
calculate the percentage of the networkday!

All fixed now, much easier to get my head around too!

cheers
Pete