+ Reply to Thread
Results 1 to 5 of 5

Mixed Text and cell ref - Problem with date formatting

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Mixed Text and cell ref - Problem with date formatting

    I am using the following formula in a cell as follows:
    ="John was born on "&A1&" at New York City hospital."

    The result I am getting is as follows:
    John was born on 39346 at New York City hospital.

    My question is, how do I modify my formula to convert to a date? I want the result to look as follows:
    John was born on 09/06/1968 at New York City hospital.

    Thanks in advance for any comments.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Title not very meaningful. Please read rules below and try a bit harder next time

    try

    ="John was born on "&TEXT(A1,"dd/mm/yy")&" at New York City hospital."

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Mixed Text and cell ref - Problem with number formatting

    Thanks for the information:

    One more question.

    I am using the following formula in a cell as follows:
    ="John owes Jim $"&A1&" for mowing the lawn."

    The value in A1 = $35.50
    The result is: John owes Jim $35.5 for mowing the lawn.

    I want the resulst as: John owes Jim $35.50 for mowing the lawn.

    I tried using formula:
    ="John owes Jim $"&NUM(A1,"0.00")&" for mowing the lawn."
    I just receive an error message.

    Thanks for the help.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    ="John owes Jim $"&TEXT(A1,"#,##0.00")&" for mowing the lawn."

    VBA Noob

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    ="John owes Jim "&DOLLAR(A1)&" for mowing the lawn."

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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