Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Date to Text in Format yyyy-mm-dd

    This request happens to be for Excel 2010, but I believe that the formula would be valid in any version.

    We use the function =TODAY()-1 to get yesterday's date. We have a database where the data for the date is text in the format yyyy-mm-dd. I tried the formula =YEAR(c2)&"-"&MONTH(c2)&"-"&DAY(c2) which gets me for example 2011-6-30, but since the data in the database is 2011-06-30, we get NA.

    Is there any function that will give me alwasy a 2-digit month and 2-digit day? I remember seeing something but could not easily find it now.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - will this work....
    DATEVALUE(C2) in some other cell
    format as a date

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts

    One Possibility a UDF

    Here's a User Defined Function that will do what you want. There may be a direct way but this does work.
    Note the -1 in the UDF assumes that the referenced cell contains the current date. If your spreadsheet doesn't contain the current date to reference you can add it or modify the function so it doesn't require a parameter.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts

    Another possibility?

    You could also use a custom format see below. I don't know which of these will render the proper format to match up with your DB but at least you have options.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks, DATEVALUE gets me a format for the date, but I need the result to be text to match or lookup what is in the database.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    This user defined function works! Thank you, Retired Geek.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    You're welcome. Did you try the custom format?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    =Text(C2, "yyyy-mm-dd")

    Steve

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    How did I know you would come up with a "Better Way"?
    Thanks.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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