I get paid on the first of every month. When this falls on a Saturday or Sunday I get paid on the previous Friday. But when this falls on a Bank holiday I get paid on the previous working day. How do I calculate my pay day?
(Previously posted on incorrect forum - sorry!)
I get paid on the first of every month. When this falls on a Saturday or Sunday I get paid on the previous Friday and I can calculate that easily But when this falls on a Bank holiday I get paid on the previous working day. How do I calculate my pay day? In 2018 the 1st April is a Sunday. Normally I would get paid on Friday 30th March. But in 2018 that is a bank holiday (Good Friday) and so I have got paid today the last workday before 1st April where 1st April is not a workday.
In reverse - when a direct debit is due on a weekend or a bank holiday, I can calculate the next working day using =workday.
In cell B2, put the following formula in (make sure to press CTRL + SHIFT + Enter when committing the formula):
The formula checks the first of the month to see if it is a bank holiday (which only fall on weekdays). If it is, it assumes payment on the following day (but just in case, it checks if the next day is a weekend, and keeps going until it finds a business day). If it is not a holiday, it checks to see if it is a weekend, and adjusts backwards until it finds a Friday.
In cell C2, put the following formula in (just as a check to see if it is going to be different that just the first day of the month, based on number of days offset):
Copy the formulas in B2 and C2 down to the end of the data.