Office & Productivity Software forum

General discussion

Excel. Using "Concatenate" to display a date.

Hi all.

I am trying to use Excel's Concatenate expression to mix words, a cell reference that holds a MAX figure, and a cell reference that holds a date.

The formula I am using is;
=CONCATENATE("Max= ",MAX(C6:C750),"p"," on ",J3)

C6:C750 holds a series of amounts. The "p" is pence, or it would be cents in the US. J3 holds the date when the MAX was achieved.

This almost works, but it only gives me Max= 202.2p on 38735

The last figure, 38735 is the date in non-date format, and I cannot get it to show in date format, eg 1/8/07, (8th Jan). Using Format > Numbers on the cell, then "Date" does not change it to a date.

Is there any way around this?

All suggestions gratefully received.

Mark

Discussion is locked
You are posting a reply to: Excel. Using "Concatenate" to display a date.
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel. Using "Concatenate" to display a date.
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: formatting a date

In reply to: Excel. Using "Concatenate" to display a date.

Mark,

In stead of J3, use Format(j3,"yyyy-mm-dd") or whatever date format you prefer. That's the way to transfer an integer (representing the date as the number of days since 1901-01-01 to a string that can be concatenated as any other string.

Kees

Collapse -
Ahh, but that's the problem

In reply to: Re: formatting a date

Hi Kees,

I thought for a while there I was going to be able to say, "You're brilliant!". But sadly, no! Happy

The problem is I cannot use the FORMAT function in the cell itself because it is a Concatenate cell, linking words and other cell contents together, ie;

=CONCATENATE("Max= ",MAX(C33:C788),"p"," on ",FORMAT(J3,"dd-mm-yy"))

Sorry, I have since changed some of the cell references

But that expression above just gives the #NAME? error.

I've just noticed something. I'm using Office XP 2002 version of Excel, and there is no function called FORMAT. It looks like I will have to use other ways to do this.

Thanks for having a go.

Mark

Collapse -
Re: formatting

In reply to: Ahh, but that's the problem

Mark,

Sorry, totally my fault. It's the difference between Dutch and English. The function isn't format, it's text(j3,"dd-mm-yy").

http://www.techonthenet.com/excel/formulas/index.php is a handy function reference in the net. Or http://office.microsoft.com/en-us/excel/CH062528191033.aspx (for 2003) and http://office.microsoft.com/en-us/excel/CH100645021033.aspx (for 2007). Or http://www.jaxworks.com/functions.htm#12.
Or use the function wizard (Insert>Function or an icon on the task bar).

Hope this helps.


Kees

Collapse -
Ohhhh.....

In reply to: Re: formatting

TEXT works perfectly Kees.

Excellent, and thank you very much. You're a pal for life now, Happy

Mark

Collapse -
Kees, I just found this as well....

In reply to: Ohhhh.....

It seems I don't have to use CONCATENATE.

I can use

="Max = "&TEXT(C8,"0.00")&"p on "&TEXT(C9,"dd-mm-yy") which gives Max = 202.45p on 8/1/07

which uses the & sign instead of Concatenate.

Those Help files are really useful at times, <g>.

Mark

Collapse -
(NT) Glad I could help, m8.

In reply to: Kees, I just found this as well....

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

SMART HOME

This one tip will help you sleep better tonight

A few seconds are all you need to get a better night's rest.