+ Reply to Thread
Results 1 to 6 of 6

Formula for "MMM & "." & DD" Error

  1. #1
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    Formula for "MMM & "." & DD" Error

    Hello,

    I am trying to build a data formula that will grab today's month and date and format it as follows - "Oct. 17".

    The formula below works when it is split into two parts (month and then date), but does not work when I combine it into two. The error message I received is #VALUE.

    I appreciate any insight and guidance. Please let me know if you may have any questions. Thank you!


    =TEXT(MONTH(TODAY()),"mmm"&"."&DAY(TODAY()))

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

    Re: Formula for "MMM & "." & DD" Error

    Try this:

    =TEXT(TODAY(),"mmm. dd")

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,649

    Re: Formula for "MMM & "." & DD" Error

    Incorrect parenthesis. Try this:

    =TEXT(MONTH(TODAY()),"mmm")&"."&DAY(TODAY())
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula for "MMM & "." & DD" Error

    Try this:

    =TODAY()

    Then format as Custom, mmm.dd
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    05-22-2017
    Location
    Houston, Texas
    MS-Off Ver
    2013/2016
    Posts
    20

    Re: Formula for "MMM & "." & DD" Error

    Thank you all. Your solutions worked perfectly.

    Follow-up question however - I use a vlookup/match formula that searches through a Power Query table based on the formula for "MMM & "." & DD".

    I get a a #N/A error message when I use the formula. However, the value from the table comes up when I type it in the cell manually as a 'mmm.dd' date.

    Is this due to vlook/match formula unable to pull from a table or because the formula is not formatted correctly formulaically? I appreciate your insights.

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

    Re: Formula for "MMM & "." & DD" Error

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

+ 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: 5
    Last Post: 02-05-2019, 12:03 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  4. = EMBED("Forms.ComboBox.1","") error "Reference not valid."
    By CatharinaCatharina in forum Excel General
    Replies: 2
    Last Post: 12-11-2014, 09:58 AM
  5. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  6. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  7. Replies: 5
    Last Post: 10-12-2010, 06:46 AM

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