I require a list for 20- 30 years in this format .

Description    First day of the month   Last day of the month
2017 January,  2017-01-01,              2017-01-31
2017 February, 2017-02-01,              2017-02-28

How can I create such lists with excel? Simple dragging doesn't work.

2 Answers 2


Formula for B3:


for C3:


enter image description here


Use the EDATE() function:

1) Input your starting date in B1, i.e. 2017-01-01. In B2, use this formula: =edate(B1,1) - this adds 1 month to the date. Copy the formula in B2 down for as many rows as you'd like.

2) In C1 you want the last date of the month - which is also the day before the first day of the following month, so you can use the following formula in C1: =edate(B1,1)-1 - this adds 1 month to the date and takes away a day, ie the last day of that month. Again, copy down as required.

3) In A1 you want the name of the month referred to in B1. So use this formula: =TEXT(B1,"yyyy mmmm") - (you can add a comma after mmmm if you specifically want it as in your question, i.e. =TEXT(B1,"yyyy mmmm,") ). Copy down.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .