cannot format date at all

[JBW]

I have exported from SAP system some data into excel.

I need to work out the week number in some dates 12.03.2007 format, for some
reason weeknum will not work at all, I thought maybe if I changed the format
to 12 march 2007 it would work (did on another sheet) but I cannot, no matter
what change the format, it's as if it's locked.

How?

 

[Richard Edwards]

have you tried converting it to text? =text(A1, "dd.mm.yyyy")

 

[Allllen]

imagine you have 12.03.2007 in cells A1.
To excel, that is just a bit of text and you can't handle it like a date.

in cell A2, use =date(right(A1,4),mid(A1,4,2),left(A1,2))

this will give you a number like 38492

now you can format this number as a date
you can use calculation with it
you can sort it properly
you can use weeknum on it

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>DMY>Finish


Gord Dibben MS Excel MVP