+ Reply to Thread
Results 1 to 6 of 6

Peculiar output coverting Date to Text

Hybrid View

  1. #1
    Registered User
    Join Date
    12-16-2015
    Location
    Anglesey
    MS-Off Ver
    13
    Posts
    4

    Question Peculiar output coverting Date to Text

    I tried to turn =TODAY() into text using the formula below with today's date being 16/12/2015 in cell A2 but got 'January' as the output month. I was expecting 'December'

    =DAY(A2)&" "&TEXT(MONTH(A2),"mmmm")&" "&YEAR(A2)

    Output = '16 January 2015'

    What have I done wrong?

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Peculiar output coverting Date to Text

    Try

    =DAY(A2)&" "&TEXT(A2,"mmmm")&" "&YEAR(A2)


    Or really, just
    =TEXT(A2,"d mmmm yyyy")

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Peculiar output coverting Date to Text

    The reason yours was returning January

    TEXT(MONTH(A2),"mmmm")

    The MONTH function returns a NUMBER from 1-12 based on the month, 1=January, 2=February etc..
    So it's currently december so that becomes
    TEXT(12,"mmmm")

    Dates are really just numbers incrimenting by 1 since January 1 1900.
    1 = January 1 1900
    12 = January 12 1900
    32 = February 1 1900
    etc...
    42354 = December 16 2015

    So 12 is January 12 1900
    TEXT(January 12 1900,"mmmm") = "January"

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Peculiar output coverting Date to Text

    If all you are doing is trying to show teh date in a different format, have you considered just changing that format in the format settings? You could maybe use Custom settings if there is not 1 that suites you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-16-2015
    Location
    Anglesey
    MS-Off Ver
    13
    Posts
    4

    Re: Peculiar output coverting Date to Text

    Should read TEXT(A2, "dd mmmm yyyy")

    Thanks everyone for your help. Perfectly obvious now and simplest is TEXT("dd mmmm yyyy") which I thought I had tried!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Peculiar output coverting Date to Text

    You're welcome.

    Are you sure you want "dd...
    If it's say December 5th for example it would end up 05 December
    But just d.. Would be 5 December

+ 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] Converting a date to a text output
    By Benoj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2013, 06:29 AM
  2. Replies: 1
    Last Post: 01-16-2013, 10:56 AM
  3. Coverting numbers to date format
    By oparman in forum Excel General
    Replies: 4
    Last Post: 09-29-2009, 11:07 AM
  4. Coverting text to numeric
    By Steve Williams in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-27-2006, 04:45 PM
  5. Coverting string of text into individual sums?
    By Pookie76 in forum Excel General
    Replies: 4
    Last Post: 03-29-2006, 01:22 PM
  6. Text output of a date/time field
    By Bob Richardson in forum Excel General
    Replies: 2
    Last Post: 11-05-2005, 12:50 AM
  7. Coverting Formula to Text
    By Catch 22 in forum Excel General
    Replies: 3
    Last Post: 03-31-2005, 09:06 PM
  8. Coverting Dates to Text
    By scott in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-04-2005, 08:06 PM

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