Solved: date formulas ?

[mercmannick]

hi

i have this formula [vba]=WEEKNUM(TODAY(),1)[/vba]

which i use to get weeknumber

which i then use this

[vba]=IF($I$2<10,$H$2&".0"&$I$2,$I$2&"."&$H$2)[/vba]

where H2 has the year in "2006" and I2 has the formula above in.

to give me the date in this format 16.2006

i then use a vlookup on this date value being the lookup value from table array on another sheet

the problem i am getting is in the vlookup box it recognises as 16.2006 , but returns a #n/a , if i manually put date in it is fine

Any Suggestions ( and is there an easier way to get the date in the format i want)


Also Posted on http://www.mrexcel.com/board2/viewto...=994580#994580



Regards

Merc

[mercmannick]

Solved , from post on MrExcel


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:

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

or use the WEEKNUM function:

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

[lucas]

Hi Merc, thanks for posting your solution. Thread marked solved