cannot format date at all

  • Thread starter Thread starter JBW
  • Start date Start date
J

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?
 
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
 
Data>Text to Columns>Next>Next>Column Data Format>Date>DMY>Finish


Gord Dibben MS Excel MVP
 
Back
Top