Date Format

[PondDweller]

Hi All,

I'm having a little trouble with a date format.

From an initial date taken from another worksheet, I'm calculating the previous 6 days using;

=DATE(YEAR(A8),MONTH(A8),DAY(A8)-1)

in the rows above, a7 to a1. So now I have a list of dates eg. a1 contains 01/12/08, a2 contains 02/12/08 and so on...

I then want to concatenate this date with another number applying the function;

=CONCATENATE(A2,"_",C2)

The problem is I just get the number 39783_1, as in the number of days since 1/1/1900_the contents of the other cell. I've tried all the various date options and still can't get it to show the date in dd/mm/yy format.

Any ideas?

Thanks

[teylyn]

Hi,

you need to convert the date into a text string with the desired format, otherwise Excel defaults to the General format, which for a date is just a big number

Try

=CONCATENATE(TEXT(A2,"dd/mm/yy"),"_",C2)

If C2 contains a number as well, it might be safer to wrap it in a TEXT() function, too.

'zat help?

[shg]

You can simplify a little.

In A7 and copy up, =A8-1

For the catenation, =TEXT(A2, "dd/mm/yyyy\_") & C2

[PondDweller]

Thank you for your responses, all appears to be functioning.

How do I add the 'Solved' qoute to the mail?

PondDweller