SOLVED

Contributor

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 have a column at right with a due date value on it and a corresponding \$ amt static value.

Based upon the column date range if the date in first column matches the range in a column, enter the value of that date:

 due amt dec31-jan6 jan7-jan13 jan14-jan20 jan21-jan27 jan28-feb3 1 \$  100.00 \$     100.00 3 \$    50.00 \$        50.00 10 \$    26.00 \$       26.00 15 \$    54.00 \$         54.00 20 \$    19.00 \$         19.00 25 \$  150.00 \$       150.00 28 \$  200.00 \$     200.00 29 \$    40.00 \$       40.00 Sum \$     150.00 \$       26.00 \$         73.00 \$       150.00 \$     240.00

I would want to be able to extend this as long as I want to with no real end date.

Not sure how to accomplish the range columns and then the match date and range formula.

3 Replies

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

@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.

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

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.
best response confirmed by DayneJake (Contributor)
Solution

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

@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.