+ Reply to Thread
Results 1 to 8 of 8

Determine last and current month (2018 - 2019)

  1. #1
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Determine last and current month (2018 - 2019)

    Hi all,

    Im using these 2 formules to determine the last and current month;

    Last; =TEXT(TODAY()-DAY(TODAY());"MMM")&RIGHT(YEAR(TODAY());2)
    Current; =TEXT(DATEVALUE(1&B22);"mmm")&RIGHT(YEAR(TODAY());2)

    I use these formules to filter data.

    Now with changing from 2018 to 2019 it goes wrongl

    Last month; Dec19
    Current month; Jan19

    How can i change the formule that it doesn't automatically take the current year but in case of transition of years it shows Dec18 - Jan19?

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,467

    Re: Determine last and current month (2018 - 2019)

    Last month:
    =TODAY()-DAY(TODAY())

    Current month:
    =TODAY()

    Then format the cell as "mmm/yy"

  3. #3
    Forum Contributor
    Join Date
    10-18-2018
    Location
    Amsterdam, Holland
    MS-Off Ver
    2013
    Posts
    104

    Re: Determine last and current month (2018 - 2019)

    Works perfect, thank you!

    I would like to understand =TODAY()-DAY(TODAY())
    If i read it i would think that it should display 4 january 19 but it displays 31-12-2018.
    How does this work?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,467

    Re: Determine last and current month (2018 - 2019)

    Date is a serial number.

    Enter =Today() in A1 and format the cell as "General".

    If Today's data - day(s) it means the last day of the previous month.

    Helped?

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Determine last and current month (2018 - 2019)

    Straight from Excel Help. (F1)
    DAG
    Geeft de dag als resultaat van een datum die wordt vertegenwoordigd door een serieel getal. De dag wordt weergegeven als een geheel getal van 1 tot 31.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Determine last and current month (2018 - 2019)

    @ bakerman2,

    Dag mijn Dutchman Friend,

    Maybe;

    Geeft de dag als resultaat van een datum die wordt vertegenwoordigd door een serieel getal. De dag wordt weergegeven als een geheel serieel getal van 1 tot 31.
    Just stirring Buddy!

    Groete
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Determine last and current month (2018 - 2019)

    The man from R.S.A. is back.

    Be carefull not to stire too much because things could get smelly real quick.

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Determine last and current month (2018 - 2019)

    @ bakerman2,


    Sticks and stones...

    Just love your sense of humor.

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Amend VBA to add dd-mm-yyyy at end of file name as it show 5-2-2018 instead of 05-02-2018
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2018, 10:58 AM
  2. how to sort Week no- Year (04-2018, 05-2019, 07-2018) in pivot chart.
    By sahana108 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-23-2018, 09:07 AM
  3. Fiscal Year 2018 ( Feb 4, 2017 - Feb 3, 2018)
    By chethan1333 in forum Excel General
    Replies: 1
    Last Post: 05-09-2017, 08:40 AM
  4. Replies: 1
    Last Post: 04-29-2016, 01:28 PM
  5. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  6. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  7. [SOLVED] Pick a cell containing current month actulas based what the current month is.
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2014, 01:17 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1