Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Display date with text

    I have an Excel report with several columns of information that I want to set up the headings with text and a calculated date using VBA to write the formulas for the headings. The fixed date in R3, for example, is 02-May-06 and has a date format. I want one of the headings (column N) to display "Days 1-30 (Mar 2006)". The following formula displays the correct date "Mar 2006":
    =DATE(YEAR($R$3),MONTH($R$3)-2,DAY($R$3))
    When I try to join text to this formula, such as
    ="("&DATE(YEAR($R$3),MONTH($R$3)-2,DAY($R$3))&")"
    the cell displays "(38778)".

    I am looking for a way to display text and a formatted date in the same cell.

    Thanks.
    Jerry

  2. #2
    Join Date
    Aug 2006
    Posts
    21

    Lightbulb

    Hi, Hope this helps

    Quote Originally Posted by JerryDal
    the cell displays "(38778)".
    What you can do at this point is use this function

    FormatDateTime(sDate, vbShortDate)

    *Where sDate is that 38778 number. Using the Short Date option will suffice

    You may need to work it in to your code maybe as a cell reference but it should get the job done regardless.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think this is what you want

    ="Days 1-" & TEXT(DATE(YEAR($R$3),MONTH($R$3)-1,0),"dd") & " (" & TEXT(DATE(YEAR($R$3),MONTH($R$3)-2,DAY($R$3)),"mmm yy") & ")"

    This displays Days 1-31 (Mar 06)
    (31 days in March ??)


    Is that correct ??
    If not I'm sure you can mod to suit.


    MTB

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    MTB: your solution, ="Days 1-" & TEXT(DATE(YEAR($R$3),MONTH($R$3)-1,0),"dd") & " (" & TEXT(DATE(YEAR($R$3),MONTH($R$3)-2,DAY($R$3)),"mmm yy") & ")" is just what was needed to display a calculated month and join it with text in a cell, and the result looks like this: Days 1-30 (Mar 06)

    This is to create headings in 4 columns for 1-30 days, 31-60 days, 61-90 days and 91-120. These are just labels indicating how long a bill is overdue and they do not represent the number of calendar days.

    Spiderman, thanks for your reply too.
    Jerry

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Correction: this is the solution I used to display a calculated date along with text in a cell:
    ="Days 1-30" & " (" & TEXT(DATE(YEAR($R$3),MONTH($R$3)-2,DAY($R$3)),"mmm yy") & ")"

    When $R$3 = 05/02/2006, the result is: Days 1-30 (Mar 06)

    Jerry

Posting Permissions

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