Forum Discussion

DayneJake's avatar
DayneJake
Brass Contributor
Oct 12, 2022
Solved

budget worksheet- Friday-Thursday in columns, match date range in rows

I want to be able to have a worksheet that has the date ranges from Friday to Thursday as the column header.  These should be sequential across the columns.  I may or may not add the year in. Then h...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Oct 12, 2022

    DayneJake OK I updated the attached using the following:

    =IF(ISNUMBER(MATCH($A3,DAY(SEQUENCE(7,,C$1)),0)),$B3,"")

    which creates a sequence of 7 days starting on the header day and checks if the "due" day is in that set. 

mtarler's avatar
mtarler
Silver Contributor
Oct 12, 2022

DayneJake so a couple questions and comments:

first instead of the date range as the header I recommend just using 1 date (start or end of the range your choice). Alternatively you could keep your range but have a helper row with the individual date.  This will make the formula much easier as you wont have to parse the text value of the header to figure out the dates.

next question is what exactly is "DUE"?  If you are extending it I assume it is NOT the "day" since the "day" like the 10th would happen each month.  so is it # days from the first date or is it the Year-Day as in the day 1-365 (or 366) in a year?

but basically then you create a formula to check if the due value is >the value in 1 column and < the value in the next column and if so return the value.

example is attached with autogen the Thursdays across the top and formula for the grid.

  • DayneJake's avatar
    DayneJake
    Brass Contributor
    Oct 12, 2022
    Thanks. I think with some of your suggestions I can get what I need.
    Yes, the Due is the date of the month it is due, so 3 or 10 means due the 3rd of every month or the 10th, respectively.
    I will try some things and if need help ask more, or post what I got to work out.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Oct 12, 2022

      DayneJake OK I updated the attached using the following:

      =IF(ISNUMBER(MATCH($A3,DAY(SEQUENCE(7,,C$1)),0)),$B3,"")

      which creates a sequence of 7 days starting on the header day and checks if the "due" day is in that set. 

Resources