getting Week number

Cbrine

Well-known Member

Joined

Dec 2, 2003

Messages

3,196

• Apr 18, 2006

• #2

I would suggest you try using this.

1. Highlight the values in the lookup table.
2. Select data....text to Columns from the excel menu.
3. Press next twice.
4. Select text in the radio buttons.
5. Press Finished.

Try the vlookup again.
If this doesn't work, then execute the process above again, but select general instead of text.

One other thing, is trailing and leading blanks. You can't see them but they make the values different. To test, you can use the Len(Cell Address) on matching values. They should have the same length, if not, the one with the higher value has extra spaces somewhere. Use the Trim(Cell Address) to remove them.

HTH
Cal

 

Upvote 0

M

MDCurry

Board Regular

Joined

May 3, 2005

Messages

188

• Apr 18, 2006

• #3

My guess is the #N/A error is because you are looking for text in an array of numbers. When you concatenate numbers Excel treats the result as text.

This formula will return a number (16.2006). It assumes you are counting week number starting from Jan 1. (test to make sure you get week you want)

=ROUNDUP((TODAY()-DATE(YEAR(TODAY()),1,1))/7,0)+(YEAR(TODAY()))/10000

you can custom format the result as "0#.####" to view leading zeros.

 

Upvote 0

mercmannick

Well-known Member

Joined

Jan 21, 2005

Messages

730

• Apr 18, 2006

• #4

Cbrine

thnks m8 tht did the trick

MDcurry

formula works a treat thnks

can you explain it a little so i can understand

Many thnks

Merc

 

Upvote 0

Cbrine

Well-known Member

Joined

Dec 2, 2003

Messages

3,196

• Apr 18, 2006

• #5

If you have a value of 16.2006, it can either be a numeric value, or an alphanumeric value(ie. Meaning a text value). The vlookup will find a numeric value vs a numeric value, or a aplhanumeric value vs an alphanumeric value. It will not find a numeric value vs an alphanumeric value.
The process I gave you just converts whatever is highlighted to numeric(General) or alphanumeric(text). Once they match your vlookup will work.

HTH
Cal

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Apr 18, 2006

• #6

Another formula that might be easier to follow, but uses the WEEKNUM function you started with is...

Code:

=WEEKNUM(TODAY())+(YEAR(TODAY()))/10000

Format as MDCurry mentioned above.

 

Upvote 0

M

MDCurry

Board Regular

Joined

May 3, 2005

Messages

188

• Apr 18, 2006

• #7

Sure thing...

=ROUNDUP((TODAY()-DATE(YEAR(TODAY()),1,1))/7,0)+(YEAR(TODAY()))/10000

First part =ROUNDUP((TODAY()-DATE(YEAR(TODAY()),1,1))/7,0)
gets the week by calculating:
the number of days from today "TODAY()" back to Jan 1 of this year "DATE(YEAR(TODAY()),1,1)"or 107 days.

Then divide by 7 which gets 15.2857 so you "ROUNDUP" to 0 decimal places to get 16.

Second part +(YEAR(TODAY()))/10000
gets the current year and divides it by 10,000 thus 2006 becomes .2006

Add the 2 together to get 16.2006.

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Apr 18, 2006

• #8

The ROUNDUP function as listed does not give the week number as mercmannick originally posted. He asked for

Code:

=WEEKNUM(TODAY(),1)

Which asks for the week to start on SUNDAY. the ROUNDUP code starts the week on MONDAY, plus shows January 1st as week zero.
The formula can be altered as below:

Code:

=ROUNDUP((TODAY()+1-DATE(YEAR(TODAY()),1,1))/7,0)+(YEAR(TODAY()))/10000

or use the WEEKNUM function:

Code:

=WEEKNUM(TODAY())+(YEAR(TODAY()))/10000

 

Upvote 0

mercmannick

Well-known Member

Joined

Jan 21, 2005

Messages

730

• Apr 18, 2006

• #9

Datsmart
and
MDCurry

many thanks works a treat , and gained a little knowledge on way

Merc

 

Upvote 0