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?

Do you really need a tick box to mark when you are called out? I think it's redundant. Whenever any data shows in 'Duration' column, it means that you are called out. When it's empty - you are not and you get only your standby rate. — Krzysztof Dołęgowski
– Krzysztof Dołęgowski, Commented Mar 23, 2021 at 18:44

Swen · Accepted Answer · 2021-03-23 20:10:09Z

2

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))

answered Mar 23, 2021 at 20:10

Swen

5772 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Krzysztof Dołęgowski · Accepted Answer · 2021-03-23 19:03:39Z

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))

Collectives™ on Stack Overflow

Automatic calculation based on date and tickbox state

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related