Automatic calculation based on date and tickbox state

Question

I am trying to build a spreadsheet to track and automatically calculate money when I am called out for work.

Here are the conditions:

• Monday, Tuesday, Wednesday, Thursday - Standby Rate: £21
• Friday, Saturday, Sunday, Bank Holidays - Standby Rate: £26
• Monday, Tuesday, Wednesday, Thursday - Callout Rate: (Hours Worked * Hourly Rate) * 1.25
• Friday, Saturday, Sunday, Bank Holidays - Callout Rate: (Hours Worked * Hourly Rate) * 1.5

I have a spreadsheet containing the following information:

• Column A - Date | Date
• Column B - Called Out | Checkbox, tick if yes
• Column C - Duration | If called out, how long for
• Column D - Calculation | Shows the calculation used to determine payment
• Column E - Payment | Shows the payment

The sheet looks like this:

+------------+-------------+----------+---------------------+---------+
|    Date    | Called Out? | Duration |     Calculation     | Payment |
+------------+-------------+----------+---------------------+---------+
| 01/02/2021 |             |          | 21                  | £21     |
| 02/02/2021 |             |          | 21                  | £21     |
| 03/02/2021 |             |          | 21                  | £21     |
| 04/02/2021 |             |          | 21                  | £21     |
| 05/02/2021 |             |          | 26                  | £26     |
| 06/02/2021 | TRUE        |        2 | 26+((2*50)*1.5)     | £176    |
| 07/02/2021 | TRUE        |        1 | 26+((1*50)*1.5)     | £101    |
| 15/02/2021 |             |          | 21                  | £21     |
| 16/02/2021 | TRUE        |      1.5 | 21+((1.5*50)*1.25)  | £177.25 |
| 17/02/2021 |             |          | 21                  | £21     |
| 18/02/2021 |             |          | 21                  | £21     |
| 19/02/2021 |             |          | 26                  | £26     |
| 20/02/2021 |             |          | 26                  | £26     |
| 21/02/2021 |             |          | 26                  | £26     |
+------------+-------------+----------+---------------------+---------+

I have had some success with the following formula to get the standby rates (K1 contains my actual hourly rate):

=SUM(IF(WEEKDAY(A2,2)>4,26,21),IF(WEEKDAY(A2,2)>4,(($K$1*C2)*1.5),(($K$1*C2)*1.25)))

But I need to make it account for Bank Holidays and perform a check to see if column B is TRUE, then if it calculates the payment as dictated above.

Any ideas?

Answer 1

Your constants are.

• Standby Rate: £21 OR £26
• Hourly Rate: £50
• Always: Standby OR Called Out
• Bank Holidays

Change your table and use this

=ArrayFormula(IF((WEEKDAY(A2:A22,2)>4)+(B2:B22=TRUE),26,21)+
              IF((WEEKDAY(A2:A22,2)>4)+(B2:B22=TRUE),C2:C22*50*1.5,C2:C22*50*1.25))

Answer 2

My working spreadsheet is here https://docs.google.com/spreadsheets/d/1N7d2-W7pRTqpO9L4DvSkmm4j7vaHpJZ2fMYAq-yVVPg/copy

I made it in a few stages and tried to make it as simple as possible.

First we determine day of the week based on date:

=WEEKDAY(A4,2)

I put it in Column C for illustration only

Then I make a table with rates for each day of the week (assuming that sunday is 1st day of the week) - you see this in columns J and K

Then I set daily rate based on day of the week and 2nd column of table:

=vlookup(weekday(A4,2),$J$1:$K$8,2,false)

I don't use arrayformula here, just copy down formula, so when it's national holiday or something, you can manually change rate.

Finally I calculate payment for each day. I add standby rate to call out hours (if there are none it's just flat standby rate). I multiply hours by 1,5 for days with 26 standby rate and by 1,25 for days with 21 standby rate:

=D4+B4*50*(if(D4=26,1.5,1.25))