Conditional Formatting Formulas-attached a file

**smart_as** · 03-10-2011, 03:16 AM

Please can you help me with the following formula. I am trying to get a colour coded calendar which will show a date but when it falls on a weekend it highlights the previous Friday. The formula entered in cell AD2 on worksheet Calendar is

=IF(Notes!H18>=1,DATE(YEAR(Notes!H18),MONTH(Notes!H18)+21,DAY(Notes!H18)),DATE(YEAR(Notes!H15),MONTH(Notes!H15)-3,DAY(Notes!H15)))

This highlights the dates based on my criteria but it is showing on a Sunday. The result of my formula could be any date in the year depending on the criteria set on the worksheet called Notes.

I have attached a file which will hopefully help. I also require the same assistance with the formula AD8 on the same sheet which needs to avoid weekends also.

If I have attacked this from the wrong angle please advise. Any help is greatly appreciated.

**arthurbr** · 03-10-2011, 04:37 AM

I would suggest you enter your formula

Please Login or Register  to view this content.

in a hidden helper cell, say A15

The following will then skip weekends and return to the preceding friday

Please Login or Register  to view this content.

**smart_as** · 03-14-2011, 09:35 AM

Hi arthurbr that worked thanks a lot for your help.

**arthurbr** · 03-14-2011, 04:19 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save