Combine text and formula

  • Thread starter Thread starter twaccess
  • Start date Start date
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
 
One way

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


--


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

Regards,

Peo Sjoblom
 
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
 
Hi Twaccess!

As an alternative, you could use a custom format:

Select the cell
Format > Custom Format
"Report Date " dd mmmm yyyy
 
="Report Date "&TEXT(TODAY(),"mmmm dd, yyyy")

will give you Report Date June 05, 2004
 
Hi Ron!

Ever forget the simplest of things. I think it's Alzheimer's again but
I forget what that is.
 
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
 
="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
 
Back
Top