Help with dynamic dates

tree

New member
Joined
Oct 21, 2014
Messages
4
Reaction score
0
Points
0
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 :)
 
When is it necessary to "change the month and year"?
 
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())
 
Thanks, I still can't get it to do what I want but I will continue to play around!
 
Well, when we get to January it should change to 2015
 
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?
 
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"
 
Good morning,

It might be as simple as the year being YEAR() + if ( ** Month Field ** = "JANUARY", 1 , 0)

I apologize if I'm off.

Best of luck,
 
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.


Don't forget old EOMONTH

=EOMONTH(A2,0)+1
 
Good call Bob.

So many ways.... the simplest elude me most of the time... :llama:
 
The issue that arose with pre-2007 versions probably caused it to drop out of mind for many of us :)
 
Do you mean the intermittent problems occurring in working with pre-2007 sheets opened in compatibility mode? I know there were some issues where the ATP function didn't quite translate properly unless saving the file as a .xslx file first.
 
No I was referring to it being in the Analysis Toolpak addin which wasn't auto-loaded, so you gave these functions as a solution, and the OP would report back that it threw an error.
 
Thanks for the help guys!

I used: ="Payment due by the 1st of "&TEXT(EOMONTH(A1,1),"MMMM YYYY")

And I get: Payment due by the 1st of November 2014

:thumb:
 
No I was referring to it being in the Analysis Toolpak addin which wasn't auto-loaded, so you gave these functions as a solution, and the OP would report back that it threw an error.

When I retired a couple of years ago, the worst problem with 2007 onwards was the new formulae that came with it. My company had people with Excel 97, Excel 2000, 2003, 2007 and 2010 that were passing spreadsheets between each other after saving as .xls if necessary and then finding that some of the formulae didn't work!
The right way would have been to upgrade everyone but instead it usually happened when your PC was upgraded or some other form of justification was approved.
 
Same issue with my work environment. When you have several thousand machines (PC's/Laptops), it can really create a nightmare...
 
Yeah, I take that point, but I don't see many customers with Excel 2003 any longer.
 
Back
Top