Date range to include the last few days of the month before and the next few days of the next month?

ksandoval

Is it possible to have in a date range to include the last few days of the month before and the next few days of the next month?

My company is trying to track the summed amount of a monthly invoice based of the dates through out the month. This is what I have a for an equation:

=SUMIFS({GRAYBAR LOGISTICS SHEET2 Range 1}, {GRAYBAR LOGISTICS SHEET2 Range 26}, [MONTH DIGIT (HIDDEN)]1) + SUMIFS({ARCHIVE of GRAYBAR LOGISTICS SHEET2 Range 1}, {ARCHIVE of GRAYBAR LOGISTICS SHEET2 Range 26}, [MONTH DIGIT (HIDDEN)]1) + 3375

It works based a range I created of when you pick a month from a dropdown list it displays the numerical digit that represents the month in a hidden column. 1=January up to December=12.

But sometimes the invoice includes the last few days of the month before and the next few days of the next month.

Could someone help?

markkrebs

Sorry for the long delay. One idea is to have a look up sheet that contains the boundaries and then a helper column that looks at those boundaries and then returns the appropriate date. For example

min date column max date column return date

04/25/25 04.30.25 05/01/25

05/20/25 05/31/25 06/01/15

Then the formula would look to see if it was between min max and then use the return date else use the date on the row.

markkrebs

you need a rule set to establish those boundaries. Is there a fixed date in the next month where it should be in the last month or is it totally dependent on the first invoice date? Basically we'd need to add some helper columns and formulas to backup the number of days to slot into the proper month. Also need to consider the year when things cross 12/31 into Jan. I'll need more info but directionally this is very doable

ksandoval

• Answer to the question: There is no fixed date, it always is shifting.

• What other information do you need?

markkrebs

So if the invoice spans 2 or more months you want it to sum in the original month? are you tallying the total by day or just by Month?

ksandoval

For a better example, the invoice for April was 3/31 through 5/02. This month may just contain only May, but other months could be like April.

markkrebs

Sorry for the long delay. One idea is to have a look up sheet that contains the boundaries and then a helper column that looks at those boundaries and then returns the appropriate date. For example

min date column max date column return date

04/25/25 04.30.25 05/01/25

05/20/25 05/31/25 06/01/15

Then the formula would look to see if it was between min max and then use the return date else use the date on the row.