Formula, Excel, Different daily rate, Weekend, Weekday

[dude6571]

Hi everyone,

Based on the table below, I would like to get a formula to be able to invoice the correct rate.

Rate Business days Business hours
100% Monday to Friday (day) 6am to 8pm
125% Monday to Friday (night) 8pm to 6am
150% Saturday (day) 6am to 8pm
175% Saturday (night) 8pm to 6am
220% Sunday (day) 6am to 8pm
245% Sunday (night) 8pm to 6am
250% Bank Holiday 6am to 8pm

Any help will be highly appreciated.

[6StringJazzer]

So Friday night is considered 8 PM Friday to 6 AM Saturday, and so on for the others nights?

What do you want to invoice? You show the table to set up the rates but you don't show how you want to use the table. What is the data that you need to apply these rates to? Will your data have a start day/time and a stop day/time and you need the rates to apply to that range? Is this an hourly rate for some kind of service? What is the base rate that these percentages apply to?

[dude6571]

Hi 6StringJazzer,

Please find reply below

So Friday night is considered 8 PM Friday to 6 AM Saturday, and so on for the others nights?
Yes same for all weekdays including weekends.

What do you want to invoice?
Consultant rate: $500

Is this an hourly rate for some kind of service?
Daily

Will your data have a start day/time and a stop day/time and you need the rates to apply to that range?
Yes it is correct.

What is the base rate that these percentages apply to?
100% means 8 hours (=1 Man day) - Monday to Friday business days between this range 6am to 8pm

[6StringJazzer]

Can you show an Excel example of the data you are using to apply this rate to?

[dude6571]

Hi,

Please find attached

[JeteMc]

Based on what is presented in the file, the following seems to automate the process.
The 'Daily Rate' table is changed to show individual days of the week and bank holiday, with day and night displayed in column O.
Bank holidays are listed below the 'Daily Rate' table in column M.
The 'base rate' is placed in cell M1
Day and night rates for each day (column L) are calculated using: =M$1*K2
The percentages in F2:F17 are populated using:

Please Login or Register  to view this content.

The rates in G2:G17 are populated using: =M$1*F2
The hours in E22:E34 are populated using: =D22+(D22<C22)-C22
The Total Paid in F22:F34 are populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.