Date formatting weeknum coming out wrong

[Tradesman]

This has been part of a seperate thread but rules state that it should have its own questionsales summarised by week V2.xlsx

There is a date colomn that comnes froma csv file

most dates are in the format

DDMMYY or DMMYY

however I require the date week number of this date ( monday start day).

it corrups certain weeks- I cant get the date to format correctly to pick up the right week.

Any help/set ups I am doing wrong?

[Ace_XL]

A bit long wounded ..

=WEEKNUM(DATE(RIGHT(RIGHT(0&C2,6),2),MID(RIGHT(0&C2,6),3,2),LEFT(RIGHT(0&C2,6),2)))

[Ace_XL]

A bit long wounded ..

=WEEKNUM(DATE(RIGHT(RIGHT(0&C2,6),2),MID(RIGHT(0&C2,6),3,2),LEFT(RIGHT(0&C2,6),2)))

[benishiryo]

hi Tradesman. could you point us to which specific ones are wrong? but to pick Mon as the start day, you should use return type of "2" instead:
=WEEKNUM(C2,2)

1 starts from Sunday. seeing Ace_XL's formula, i'm not sure if i got you wrongly

[dogberry]

Hi

Are you getting the correct information from the CSV file when I converted the date numbers to actual dates they range from 18 Aug 1984 to 21st Feb 1989 is that correct?

Chris

[Tradesman]

I wish i knew how to do that - could you explain the thought process - how does right(0&c2,6) work??

[Ace_XL]

I wish i knew how to do that - could you explain the thought process - how does right(0&c2,6) work??

Firstly, does the formula give you desired results? you might want to use

Please Login or Register  to view this content.

depending on the start day being Monday

right(0&c2,6) picks only the 6 rightmost characters of 0&c2. Hence 30912 becomes 030912 and rightmost 6 characters gives you 030912, while 271112 becomes 0271112 and 6 rightmost gives you 271112; giving you consistent 6 caharcters to ascertain dates

[Tradesman]

Ho dogberry, the dates from the csv file cpome out in the dmmyy or ddmmyy range! my excels doing what yours is

=WEEKNUM(DATE(RIGHT(RIGHT(0&C2,6),2),MID(RIGHT(0&C2,6),3,2),LEFT(RIGHT(0&C2,6),2)))

this works a treat!

[Tradesman]

Thanks guy

acr-XL - superb

[icestationzbra]

if Sunday start day:

Please Login or Register  to view this content.

if Monday start day:

Please Login or Register  to view this content.

[daddylonglegs]

Many date functions in Excel can handle a text formatted date just as easily as an actual date, WEEKNUM is one of those, so in fact you could even eliminate the "coercers" -- and use

=WEEKNUM(TEXT(C2,"00-00-00"),2)

......but if you are trying to summarise by week then using WEEKNUM might not be the best way because at the start and end of the year weeks could have as few as 1 day (e.g. 2012 week 1 has only 1 day if you use Monday as the start date).

You might be better served using ISO week numbers (where all dates have 7 days - week 1 may start at the end of the previous year) or by using week start dates rather than week numbers