How to list Months between two dates?

Question

How to list Months between two dates?

I have a spreadsheet with bills that recur for approximately 12 months each. Rather than entering the bill each month for a total of 12 times I just enter it as a date range i.e. 21.01.01 - 21.01.12, energy bill, $100.

I want to display this as a monthly graph to display how bills change over time. To do this I assume I would need a separate table with an actual list of each month with the recurring bill.

Is there anyway to auto generate these months from my input table? I.e. a formula that reads the specific months & years between the two dates i entered and then lists them?

Or is there a simpler way to do what I’m look for?

Posted on Feb 6, 2022 1:54 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,126 points

Feb 6, 2022 2:30 PM in response to Michael_Bully

Here is one way

Create a lot of columns

In E1 enter January 2021. I drag-filled to the right to fill in the headers of the other columns

E2 =IF($A2≠"",IF(AND($C2≤E$1,$D2≥E$1),$B2,""),"")

Drag fill to all the rest of the rows and columns

You may have to adjust the formula (commas vs semicolons) to match your region. The dates in my table are MM/DD/YYYY.

Each column has the bill for that month but not the exact date in the month.

Having it all in one big table makes it easier to chart the data. But if you want to view the data in other tables, you can do that with formulas and you can hide the columns in this table. The wide table is not the best for viewing the data and certainly not good for printing it unless you hide columns and show only a year or so at a time.

Reply

Answer 2

Feb 7, 2022 2:33 PM in response to Badunit

This is great! It’s exactly what I was trying to do myself but was stumped on the formula. Thanks so much for the info!

Now how would I go about getting the table to pull in the same bills with different time frames? I.e. energy 21.01.01 - 21.01.12 $100, energy 22.01.01 - 22.01.02 $120.

I assume I would need a separate table with these bills entered and in the wide table use a ‘lookup’ formula combined with the formula you have written?

Reply

Answer 3

Badunit

Level 7

28,126 points

Feb 8, 2022 3:37 PM in response to Michael_Bully

Enter it into a blank row of the table.

Copy/paste the table to make a new table that is set up with the same columns

Delete columns B,C,D.

Delete all the data

Column A should have each category (Energy, Water, etc.) listed one time

Formula in cell B2 =SUMIF(Table 1::$A,$A,Table1::E) this assumes the other table is named Table 1

Drag-fill or copy/paste it to the rest of the columns and rows.

This should aggregate all "Energy" rows from Table 1 into one "Energy" row in this new table. Same for the other categories.

Just to be sure the two tables match up date-wise if you ever make a change to the dates in row 1 of Table 1, you might also want do this:

Put into cell B1 the formula =Table 1::E1

Drag-fill or copy/paste it to the rest of the row

Reply

How to list Months between two dates?

Similar questions