Combine text and formula

T

twaccess

Please could someone help me with the following :-

I'm trying to create the following string which automatically update
itself

Report Date 6th June 2004

I seem to be struggling with the syntax to achieve this.

='Report Date'&today() doesn't work ??

Thanks


TWACCES
 
P

Peo Sjoblom

One way

="Report Date "&TEXT(today(),"mm/dd/yy")


--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
R

Ron Rosenfeld

Please could someone help me with the following :-

I'm trying to create the following string which automatically updates
itself

Report Date 6th June 2004

I seem to be struggling with the syntax to achieve this.

='Report Date'&today() doesn't work ??

Thanks
Click to expand...

="Report Date " & TEXT(TODAY(),"d mmmm yyyy")

will give you the text:

Report Date 6 June 2004


--ron
 
N

Norman Harker

Hi Twaccess!

As an alternative, you could use a custom format:

Select the cell
Format > Custom Format
"Report Date " dd mmmm yyyy
 
J

JON JON

="Report Date "&TEXT(TODAY(),"mmmm dd, yyyy")

will give you Report Date June 05, 2004
 
N

Norman Harker

Hi Ron!

Ever forget the simplest of things. I think it's Alzheimer's again but
I forget what that is.
 
R

Ron Rosenfeld

Hi Ron!

Ever forget the simplest of things. I think it's Alzheimer's again but
I forget what that is.
Click to expand...

Of course, none of us bothered to give the OP what he originally requested,
which was an ordinal for the day of the month!


--ron
 
R

Ron Rosenfeld

="Report Date " & TEXT(TODAY(),"d mmmm yyyy")

will give you the text:

Report Date 6 June 2004


--ron
Click to expand...

If you really want the day to be an ordinal, one way is to use a VB routine:

Your formula might look like:

="Report Date "&ordinaldate(TODAY())

and the User Defined Function (UDF) would be:

===========================
Function OrdinalDate(dt) As String
Dim Suffix As String

If Not IsNumeric(dt) And Not IsDate(dt) Then Exit Function
'Dates returned from functions, like TODAY(), will fail IsDate

Select Case Day(dt)
Case Is = 1, 21, 31
Suffix = "st"
Case Is = 2, 22
Suffix = "nd"
Case Is = 3, 23
Suffix = "rd"
Case Else
Suffix = "th"
End Select

OrdinalDate = str(Day(dt)) & Suffix & Format(dt, " mmmm yyyy")
End Function
====================

To enter the UDF, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer
Insert/Module
Paste the above code into the window that opens.


--ron
 
Top