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
Bookmarks