Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Help with dynamic dates

  1. #1

    Question Help with dynamic dates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    When is it necessary to "change the month and year"?


  3. #3
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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())

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

  5. #5
    Well, when we get to January it should change to 2015

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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?


  7. #7
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    =YEAR(TODAY()) won't work?

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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"


  9. #9
    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,

  10. #10
    Quote Originally Posted by NBVC View Post
    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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •