How do I create a list of each month with first day of the month and last name of the month

Question

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.

Answer 1

Formula for B3:

=DATE(2017,1+ROW()-ROW($B$3),1)

for C3:

=DATE(2017,2+ROW()-ROW($B$3),1)-1

Answer 2

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.