Forum Discussion
Solved
Excel formula help
I’m creating a spreadsheet with a number of date calculations. I have all but one set figured out and cannot for the life of me get it to do what I need it to do. I need a formula that will take a date from one cell, subtract exactly 1 week weekends included so like Monday to Monday not business days, and if that day falls on a holiday, fall back to the previous day or if the holiday is a Monday, to fall back to the previous Friday. I can either get it to work doing exactly 1 week but still results with the holidays, or it to work with the holiday using business days but the ones he following week with no holidays is wrong. Ugh 🤦🏼♀️
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.
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?
Thank you SO much! This worked perfectly! ☺☺☺☺☺
