Return "N" date and conditional format help

[Classixz]

Hi all,

My knowledge of Excel is quite basic but I'm just after some guidance please.

I'm trying to set up a spreadsheet (attached) to help with monitoring my payroll processing and hopefully to help any new starters. Basically, the pay date for our customers is the 26th which rolls back to Friday's date if the 26th fall on the weekend and the spreadsheet does this. However, I would also like it to take into consideration bank holidays. How can I edit the formula in C6 to take into consideration the Bank Holiday dates I have set up on the seperate tab?

So for example, if the 26th falls on a Saturday, I need it to return the date for the 25th. If the 25th happened to be a Bank Holiday, I would then need it return the date for the 24th instead.

I also need to put a conditional format on the cells E16 - X27 to highlight the cell if the cell is blank and the deadline date is due "tomorrow" (deadline dates will be specified in cells I4 to I9). However, if the due date falls on a Monday, I still need the cells to highlight showing the Monday as the next working day due date - if that make sense? Again, taking bank holidays into consideration. So if the Monday was a Bank Holiday, I would need the cell to be highlighted using the Tuesday as the next working day due date.

I don't know if I have explained that well or whether the above is possible but any help/feedback would be much appreciated.

(The spreadsheet is a bit untidy at the moment as it is a working progress)

Thank you

[ORoos]

Hi there,


To account for the Bank Holidays / Public Holidays, you need to add the reference to your formula.
In cell C6, try: =WORKDAY(C4+26,-1,'Bank Holidays'!A5:A15)

Will look at your conditional formatting separately.

Regards,

[ORoos]

Hi Again,

I have calculated the working day befroe the due date in cells I4:I9 and placed in cells J4:J9.
I then linked the first couple of dates on top of your table E12:F12 (not sure which columns relate to what dates).

For the conditional formatting, I did the following 2 rules for columns E & F - Apply to: =$E$16:$F$20,$E$22:$F$27

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know if that works for you.

PS: I added a couple of Bank Holidays to the list for testing.

[Classixz]

Hi ORoos,

Many thanks for your help with this. The =WORKDAY(C4+26,-1,'Bank Holidays'!A5:A15) formula works a treat - thank you.

With regards to the conditional formatting, I'm not sure if I'm misunderstanding how the formula works? I need it to do 2 things, highlight the cell if the "deadline" date is due today or passed, this seems to be working ok and to also highlight the cell (a different colour) if it is due tomorrow.

Let's say (for example) cell E16, if the 'Deadline' date is tomorrow (Deadline date found in cell I4), I need the cell E16 to be highlighted as a "warning" to say it is due tomorrow but tomorrow needs to be the next working day (and to take into account the bank holidays). For example, if today is Friday 25th and the deadline date is Monday 28th, I need the cell to be highlighted to show that it is due "tomorrow", as a warning. If that Monday the 25th happened to be a bank holiday, I need it to pick up any deadline dates for Tuesday 29th instead and highlight the cells as a 'warning - due date tomorrow' conditional format.

Hope that makes sense?

[ORoos]

Hi Classixz,

In the example for E16, we have the due date on WED the 27 Jan. We also have 2 bank holidays (for testing) on MON 25th and TUE 26th. So the last workingday before the due date is FRI 22nd. I assume this is the day you like to be highlighted.
Please refer and validate the dates to ocnfirm I got your requirements correct. The date before due date is in column J (or E13 where I copied the date for easier reading. the formula also works of the date in this cell. Adjust to J4 if you prefer the dates up there.)

The previous formula only would highlight on the actual day; 22nd, not over the weekend or during the bank holidas.

I have slightly modified the formula for the conditional formatting for the rule for the working day before the due date.

=AND(E16="",TODAY()>=E$13)

Test 2: If you delete the test bank holiday for the 25th Jan, the day before due date will be the 25th as the 26th is still a bank holiday and due date 27th.
Test 3: If you now delete the second test bank holiday for the 26th Jan too, the day before due date will be the 26th, due date still 27th.
Note: Highlight will only if cell is empty (no date entered).

Hope I understand you correctly.

[Classixz]

Hi ORoos,

This now seems to be working as needed. Thank you so much for your time and help with this.

[ORoos]

Glad to hear this is working for you, and thanks for the Rep .

Could you please take a minute and mark the question as 'Solved'. You can do this via the Threadtools at the top of your original post.

Thanks.