formula to display all the months between two dates

[bleeet]

Hi guys

does anyone happen to know a formula I can use that will display all the months between a start date and the end date?

ultimately I want to make a diagram that displays all the months from start to end between two dates


thanks

 

[kbrummert]

Does the result have to be in one cell, or a varying range of cells--one for each month?

 

[bleeet]

Does the result have to be in one cell, or a varying range of cells--one for each month?

spread out across a range of cells..yea one for each month.

 

[Damo71]

So lets say your start date is in A1 and your end date is in B1.

If you want your results to include the start and end months, then in D1 type:
=EDATE(A1,0)

then in E1 type:
=IFERROR(IF(EDATE(D1,1)>$B$1,"",EDATE(D1,1)),"")


If you want your results to exclude the start and end months, then in D1 type:
=EDATE(A1,1)

then in E1 type:
=IFERROR(IF(EDATE(D1,1)>=$B$1,"",EDATE(D1,1)),"")

Then copy to the right for as many cells as you're likely to need. (so if there's usually only 1 year between the two dates, then you need to copy over to 12 cells)

Perhaps format the cells as "mmm yy", otherwise you may and up with a serial number instead of a date

 

[bleeet]

So lets say your start date is in A1 and your end date is in B1.

If you want your results to include the start and end months, then in D1 type:
=EDATE(A1,0)

then in E1 type:
=IFERROR(IF(EDATE(D1,1)>$B$1,"",EDATE(D1,1)),"")


If you want your results to exclude the start and end months, then in D1 type:
=EDATE(A1,1)

then in E1 type:
=IFERROR(IF(EDATE(D1,1)>=$B$1,"",EDATE(D1,1)),"")

Then copy to the right for as many cells as you're likely to need. (so if there's usually only 1 year between the two dates, then you need to copy over to 12 cells)

Perhaps format the cells as "mmm yy", otherwise you may and up with a serial number instead of a date

thank you I will try this out

I have another question can you explain this =IFERROR(IF(EDATE(D1,1)>$B$1,"",EDATE(D1,1)),"") I trying to understand the $B$1

thanks again

 

[Damo71]

thank you I will try this out

I have another question can you explain this =IFERROR(IF(EDATE(D1,1)>$B$1,"",EDATE(D1,1)),"") I trying to understand the $B$1

thanks again

B1 is where I put the ‘end date’ In my example.
So the formula is saying “if the cell to the left +1 month is greater than the end date, then show nothing, Otherwise show the cell to the left +1 month”
It is surrounded by dollar signs to make it an absolute reference (so when you copy the formula to the left, the focus stays on B1)

 

[bleeet]

B1 is where I put the ‘end date’ In my example.
So the formula is saying “if the cell to the left +1 month is greater than the end date, then show nothing, Otherwise show the cell to the left +1 month”
It is surrounded by dollar signs to make it an absolute reference (so when you copy the formula to the left, the focus stays on B1)

thanks so much

sorry I am asking because I am trying to learn how to do this myself

 

[Damo71]

thanks so much

sorry I am asking because I am trying to learn how to do this myself

You're welcome. I’m only learning myself, and there are probably more efficient ways to do it than my suggestion. But that’s how I would do it at my current skill level.