Forum Discussion

JenniL0211's avatar
JenniL0211
Copper Contributor
Aug 31, 2018

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 🤦🏼‍♀️
Formulas and Functions
  • Philip West's avatar
    Philip West
    Aug 31, 2018

    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.

  • Philip West's avatar
    Philip West
    Steel Contributor
    Aug 31, 2018

    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.

    • JenniL0211's avatar
      JenniL0211
      Copper Contributor
      Oct 30, 2018

      Thank you SO much!  This worked perfectly! ☺☺☺☺☺

    • JenniL0211's avatar
      JenniL0211
      Copper Contributor
      Oct 31, 2018

      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.

      • Philip West's avatar
        Philip West
        Steel Contributor
        Nov 01, 2018

        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?

Resources