Hi all,

I was halfway through writing this post when I was able to work it out myself, so I'll just post it here anyway in case anyone else has the same issue:

I had a bunch of invoices for trucks that bill with a weekly rate (5-day weeks). Any rentals over 2 days are billed for a full week. Anything 2 days or less is pro-rated based on the weekly rate.

For example:
2.6 weeks is charged for 3 weeks.
2.4 weeks is charged for 2.4 weeks.

I have created a worksheet which takes a date range and figures out the amount of 5-day weeks:
=NETWORKDAYS(A1,A2)

I then used this formula to round up if the amount after the decimal is over 0.5 and leave it alone if it was under:
=IF((NETWORKDAYS(E2,F2)/5)-(TRUNC(NETWORKDAYS(E2,F2)/5))>0.5,ROUNDUP((NETWORKDAYS(E2,F2)/5)),NETWORKDAYS(E2,F2)/5)

And that's it! All I had to do after that is multiply the weekly rate by the adjusted number of weeks to get the total amount for the truck.

Purchase orders made easy!

Cheers,
Rich