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.


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.


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.


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.


Collapse -
Re: formatting

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


Sorry, totally my fault. It's the difference between Dutch and English. The function isn't format, it's text(j3,"dd-mm-yy"). is a handy function reference in the net. Or (for 2003) and (for 2007). Or
Or use the function wizard (Insert>Function or an icon on the task bar).

Hope this helps.


Collapse -

In reply to: Re: formatting

TEXT works perfectly Kees.

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


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>.


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

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

Popular Forums

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


This one tip will help you sleep better tonight

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