Help with dynamic dates

**tree** · 2014-10-21, 02:53 PM

Hey

So, I have to prepare monthly invoices. The date at the top is fine: I just have =today() in my inputs sheet. My problem is that I also have two other inputs: 'payment for November 2014' and 'payment due by the 1st of November 2014'.

I am trying to find a way to get excel to change the month, and year when necessary, automatically but to also keep my date formatting. Does anyone know how I do this? It is probably really simple. I started making an IF statement but it is too cumbersome.

Any help much appreciated!
Thanks

**NBVC** · 2014-10-21, 03:30 PM

When is it necessary to "change the month and year"?

**Mike_Alex** · 2014-10-21, 03:31 PM

If I am understanding you correctly, you can actually use the Year(date cell) or Month(date cell) ad accomplish that. Or, for week ending you could use =today()Weekday(today())

**tree** · 2014-10-21, 04:10 PM

Thanks, I still can't get it to do what I want but I will continue to play around!

**tree** · 2014-10-21, 04:13 PM

Well, when we get to January it should change to 2015

**NBVC** · 2014-10-21, 04:23 PM

But if today's date is in October, you want always to show the next month? So really, in December 2014 is when you want it to say January 2015?

**Mike_Alex** · 2014-10-21, 04:24 PM

=YEAR(TODAY()) won't work?

**NBVC** · 2014-10-21, 04:26 PM

This will get you the next month's date (1st of the month)

=DATE(YEAR(A2),MONTH(A2)+1,1)

where A2 contains the =TODAY() formula.

To put it into your header:

="payment for " & TEXT(DATE(YEAR(A2),MONTH(A2)+1,1),"MMMM YYYY")

so starting December 1, 2014, it will read: "payment for January 2015"