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

Hi, Hope this helps

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.

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

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

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