I want to format an invoice so that the dates of

the month change to the current month just by

entering the date one time in the first cell. The

invoice would include all the days of the month.

How do I do this? Thanks for your help.

Hi, You can try this:

In cell A1 enter =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

This will return the first day of the current month & year

or just enter the date for the first of the required month.

The date must be in a format Excel recognizes as a date.In cell A2 enter =A1+1

This will give the second of the monthDrag the formula in cell A2 down to A28

This will give the first 28 days.In cell A29 enter this formula: =IF(DAY($A$1+28)<>29,"",A28+1)

This tests whether 28 days after the 1st of the month is 29 (or 1 - i.e. 1st of next month)

If it isn't 29, it returns "", else it returns the dateThe same approach is used for A30 and A31

A30 =IF(DAY($A$1+29)<>30,"",A29+1)

A31 =IF(DAY($A$1+30)<>31,"",A30+1)Format all cells with the required date format.

Hope this is the sort of thing you were looking for.

Regards

Thanks so much. That was exactly what I was looking for!!

Glad to have helped, and thanks for the feedback. Regards

Humar

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History