Excel date in numbers

[|ceman]

Hi been searching on the net and this forum on how to correct this, I've got frustrated figuring it out so I come to the point for asking some help; I'm using excel 2007, I'm trying to concatenate a date in one cell (e.g. 31/05/2011) but the result comes out in numbers (40694), how do I correct this? I've been trying to change this in the format options but to no avail,please help. thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

Example:

="Today is "&TEXT(TODAY(),"dd/mm/yyyy")

TODAY() can be a cell reference.

 

[|ceman]

Thanks Andrew, here another case that I'm having trouble with, hope you can help, thanks!

I'm getting frustrated on the date formats on my worksheet, you see, my data dump came from different sources, some from downloads, some from data sent, etc. etc. the things is, when I try to consolidate them into one dump fil (thru copy/paste) to create a comparative table between dates, the dates have different formats!!!, eg May 2, 2011 appears as figures 40665, some are in dd/mm/yyyy 02/05/2011 and inverse mm/dd/yyyy 05/02/2011 (which is what I'm aiming for) and some are just plain text!!!!, when I create the pivot table, it will not lump all the information under the same dates because they are in different formats. I try the date formatting and it never works so is the custom format. Any quick fix on this? I know there must be something to create a uniform date formats, for now, I have to copy all the dates one by one just to have the same formats which is really frustrating, hope you can help, thanks!!!!
<!-- / message -->

 

[Norie]

All of those are valid date formats, it's the underlying value that's the important thing.

If the underlying values were 'real' dates then you probably wouldn't have a problem grouping in the pivot table.

What you need to do is convert any 'dates' that aren't being recognised so they will Excel will recognise them as dates.

There are various ways you can do that, here's one:

1 Select a blank cell and copy it.

2 Select the column with dates.

3 Goto Paste Special... and select the Add option in the Operation section.

4 Press OK.

Try that, if it doesn't work post back.

By the way you can easily check for 'real' dates with this formula:

=ISNUMBER(A1)

This will return TRUE if there's a number in A1.

Since Excel stores dates as numbers, eg 40665, A1 is probably a date.

 

[|ceman]

hi norie,

true to what you said, there are dates that are not "real" dates as I used ISNUMBER formula, I tried doing what you said, I filtered all "False" in ISNUMBER formula, copied a blank cell and paste special add option, but it is still false, any other route I can take? btw, I had a date that is 5/1/2011 (May 1) and another same date that is formatted as 1/5/2011, the dates are not grouped in the pivot table, they are both "true" dates.

thanks~!