+ Reply to Thread
Results 1 to 5 of 5

Display the Month in One Cell, Produced by the formula Today() in Another Cell

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Display the Month in One Cell, Produced by the formula Today() in Another Cell

    Hi. I am looking for a formula which will display the Month in the format mmm (jan) in one cell, based on another cell which has the formula today().

    So, right now, in a2, I have the formula today(), and in a1 I have the formula month(a2). But, no matter what I do, it produces "Jan", even though it is actually May.

    Help!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Display the Month in One Cell, Produced by the formula Today() in Another Cell

    In A1 all you need is =A2 formatted as "mmm". MONTH() returns an INTEGER 1-12 not month names.
    Ben Van Johnson

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Display the Month in One Cell, Produced by the formula Today() in Another Cell

    Hi,

    As long as you have the formula
    =TODAY()
    in A2 and
    =A2
    in A1


    Then use a custom format with A1 set to display "mmm"
    You could of course just use the custom format in A2 in the first place.

    =MONTH(A2) returns the number 5, being the 5th month. But if you then format the cell to show "mmm" Excel interprets day 5 as January 5th 1900 and hence displays "Jan"
    Last edited by Richard Buttrey; 05-03-2018 at 02:33 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Display the Month in One Cell, Produced by the formula Today() in Another Cell

    You can also use this in A1:

    =TEXT(A2,"mmm")

    Just note that this will return a text value and not an Excel recognized date.
    Last edited by 63falcondude; 05-03-2018 at 02:33 PM.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Display the Month in One Cell, Produced by the formula Today() in Another Cell

    try this: =TEXT(A2,"mmm")
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. Replies: 4
    Last Post: 05-17-2016, 05:32 PM
  2. [SOLVED] FORMULA NEEDED: If a cell = certain criteria a consecutive number will be produced
    By Aravia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2015, 10:49 PM
  3. [SOLVED] Display month formula in certain cell :confused:
    By pipsmultan in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-14-2014, 09:22 AM
  4. [SOLVED] How do you display the first day of a given month using the today() function
    By john dalton in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2013, 07:42 AM
  5. condit. format: If date in cell = today, display "Today"
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 01:33 PM
  6. [SOLVED] display cell to show only Month/year to do sumif calc if today()= any day of month
    By fireguy7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2013, 06:55 PM
  7. [SOLVED] display the cell that produced a result
    By Jerry Wood in forum Excel General
    Replies: 1
    Last Post: 06-06-2005, 01:05 PM

Tags for this Thread

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