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

[Ns1]

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

 

[Cattlegod]

concatenate or &

 

[KLin]

replace + with an &

="For The Year Ended " & cyedate()

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

 

[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

 

[KLin]

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]

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]

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]

Is CYEDATE() a custom function?

 

[Ns1]

Originally posted by: KLin
Is CYEDATE() a custom function?

yes

 

[KLin]

39447 is the serial number value for 12/31/2007. That's the number of days since 1/1/1900.

 

[Ns1]

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]

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.

 

[KLin]

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]

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]

Use the TEXT() function:

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

 

[Ns1]

Awesome, thanks!

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

Works perfectly.

 

[KLin]

nvm

 

[BlueWeasel]

Is it possible to put text both before and after a function?

 

[mayest]

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!

 

[KLin]

nvm

 

[QED]

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]

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]

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.