Forum Discussion
Excel formula help
I think I can do it if two things are true.. first you have a list of holidays, and second that the holidays are single days.. like a bank holiday.
I'm attaching a work book which I think does what you described.
The formula looks something like this:
=IF(COUNTIF($K$4:$K$6,F4-7),IF(WEEKDAY(F4,2)=1,F4-10,F4-8),F4-7)
=IF(COUNTIF($K$4:$K$6,F4-7), *does our new date appear in the list of holidays?
IF(WEEKDAY(F4,2)=1, *yes, so is our date a Monday?
F4-10, *yes its a Monday, so -10days to get to friday
F4-8), *no its not a Monday so -8days to get to the day before the holiday
F4-7) * it wasn't in the holiday list so just -7days
Hope that helps.
Ok, so I thought this was working perfectly until I just found a date from the formula that is in my holiday list. Now I'm not sure what to do to make it fall back to a date that the holiday.
Hia,
has the size of the list changed, does the formula reflect that? Could you post the workbook, or at least the bits we are looking at?
Hello. No, the holiday section has not changed whatsoever. I have attached a copy of the workbook I'm using. The formulas in question are outlined in the red box. The solution to those formulas generate in boxes D12-16 and D21-25. As you can see, the dates for this week show for Wednesday as 11/22; however, that date is included in the holiday list. The dates in this column need to be exactly 1 week from the dates in E12-16 and E21-25 as specified in my initial post. It was working beautifully up until I noticed it had the holiday date in it this week :(