Excel Masters: How would I put text before a function?

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
So the program I'm using has a function =CYEDATE(). This adds the current year end to the field. I'd like to add some text before it, how would I go about this?


Ideally it would say For the year ended (current year end date)

The following don't work

="For the Year Ended" + cyedate()
='For the Year Ended" + cyedate()

="For the Year Ended " &TEXT(CYEDATE(), "mmmmmmmm dd,yyyy") = winner

----------------------------------------------
There is a S/W forum - next time use it

Senior Anandtech Moderator
Common Courtesy
 

KLin

Lifer
Feb 29, 2000
29,816
318
126
replace + with an &

="For The Year Ended " & cyedate()

Don't forget to put a space before the 2nd double quote.
 

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
Originally posted by: KLin
replace + with an &

="For The Year Ended " & cyedate()

Don't forget to put a space before the 2nd double quote.

awesome thanks.

lol


too bad it displays as this:
For the Year Ended 39447


Instead of this:

For the Year Ended December 31, 2007
 

KLin

Lifer
Feb 29, 2000
29,816
318
126
Originally posted by: Cattlegod
concatenate or &

So you posted with a . just to be the first responder. Then you edit later with an answer? :laugh:
 

darkxshade

Lifer
Mar 31, 2001
13,749
6
81
Originally posted by: Ns1
Originally posted by: KLin
replace + with an &

="For The Year Ended " & cyedate()

Don't forget to put a space before the 2nd double quote.

awesome thanks.

lol


too bad it displays as this:
For the Year Ended 39447


Instead of this:

For the Year Ended December 31, 2007

Format cell to date?
 

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
Originally posted by: darkxshade
Originally posted by: Ns1
Originally posted by: KLin
replace + with an &

="For The Year Ended " & cyedate()

Don't forget to put a space before the 2nd double quote.

awesome thanks.

lol


too bad it displays as this:
For the Year Ended 39447


Instead of this:

For the Year Ended December 31, 2007

Format cell to date?

Doesn't work.
 

KLin

Lifer
Feb 29, 2000
29,816
318
126
39447 is the serial number value for 12/31/2007. That's the number of days since 1/1/1900. ;)
 

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
Originally posted by: KLin
39447 is the serial number value for 12/31/2007. That's the number of days since 1/1/1900. ;)

I'm over it.


I was ecstatic to find the answer - this quickly became aggravation, and now I'm over it.


Thanks for the help though :)
 

KLin

Lifer
Feb 29, 2000
29,816
318
126
I justed tested this. I created a function that returns today's date as a string datatype, and it concatenates correctly.

I put the following in a module:
Public Function Test()
Test = CStr(Date)
End Function

Then put the following in a cell:
="For the Year Ended " & Test()
 

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
Originally posted by: KLin
Originally posted by: Ns1
Originally posted by: KLin
Is CYEDATE() a custom function?

yes

If you have the function return the date as a string/text value, it should work.

I has no idea what you just said lol

The field currently has

="For the Year Ended " &CYEDATE()

which returns

For the Year Ended 39447


I've used 'format cells' -> number -> category and have tried practically every logical type and nothing works...


//edit

I just read what you posted and I have no idea wtf you just did. I don't do any VBA programming in excel, the function was built into excel by a 3rd party program (ProSystems FX)

I need to learn some advanced excel...
 

QED

Diamond Member
Dec 16, 2005
3,428
3
0
Use the TEXT() function:

= "For the Year Ended " & TEXT(CYEDATE(), "mmm dd,yyyy")
 

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
Awesome, thanks!

="For the Year Ended " &TEXT(CYEDATE(), "mmmmmmmm dd,yyyy")

Works perfectly.
 

mayest

Senior member
Jun 30, 2006
306
0
0
You need to use the Text() function to format the date. Assuming that the date is in A1, do this:

="For the Year Ended "&TEXT(A1,"mmmm dd, yyyy")

Satisfaction guaranteed!
 

QED

Diamond Member
Dec 16, 2005
3,428
3
0
I had three m's, not two, which returns an abbreviated month name.
For the full month name, use four m's. Use a single "d" for no leading 0 for the day of month.

= "For the Year Ended " & TEXT(CYEDATE(), "mmmm d,yyyy")

 

KLin

Lifer
Feb 29, 2000
29,816
318
126
Originally posted by: Ns1
Originally posted by: KLin
Originally posted by: Ns1
Originally posted by: KLin
Is CYEDATE() a custom function?

yes

If you have the function return the date as a string/text value, it should work.

I has no idea what you just said lol

The field currently has

="For the Year Ended " &CYEDATE()

which returns

For the Year Ended 39447


I've used 'format cells' -> number -> category and have tried practically every logical type and nothing works...


//edit

I just read what you posted and I have no idea wtf you just did. I don't do any VBA programming in excel, the function was built into excel by a 3rd party program (ProSystems FX)

I need to learn some advanced excel...

If you wanted to get real fancy, you could do the following in a function(if the functions from the 3rd party work in VBA that is):

1. ALT-F11 to open vba editor
2. Click insert, click new module
3. Type in the following:
Public Function GetDateString() As String
GetDateString = Format(CYEDATE(), "mmmm d, yyyy")
End Function
4. Save module, give it a name, any name. Then call GetDateString() in a cell.
 

Ns1

No Lifer
Jun 17, 2001
55,419
1,599
126
1. ALT-F11 to open vba editor
2. Click insert, click new module
3. Type in the following:
Public Function GetDateString() As String
GetDateString = Format(CYEDATE(), "mmmm d, yyyy")
End Function
4. Save module, give it a name, any name. Then call GetDateString() in a cell.

Too much work, although I do use the VBA editor to break lots of "protection" embedded in excel files.