convert Week 1 to a date range

[harm.charles]

I looked at various post to what I'm trying to do, and I could not get
it in my mind as to how to apply some of the answers to what I'm
trying. The info I look at is a user input of "Week1" or "Week2" ect.
I would like to take the "Week1" and convert it to the first week of
the current year which in this case I think would be "29-2" . I need
to take in the account if its a leap year or not. I could use a
vlookup, but would prefer VBA.
Any help would be appreciated.

Thanks

 

[Bob Phillips]

Can you explain how Week is 29/2, it is more like 01/01 AFAICS.

 

[harm.charles]

Can you explain how Week is 29/2, it is more like 01/01 AFAICS.

--
__________________________________
HTH

Bob

Thanks for the reply.
Week1 should be "Dec 29 - Jan 2". 29 is the 1 workday for week 1 of
the new year 2009 and Jan 2 is the last work day of the new year.

 

[Bob Phillips]

Try this

=DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),2)+(week_num-1)*7+1

--
__________________________________
HTH

Bob

Can you explain how Week is 29/2, it is more like 01/01 AFAICS.

--
__________________________________
HTH

Bob

Thanks for the reply.
Week1 should be "Dec 29 - Jan 2". 29 is the 1 workday for week 1 of
the new year 2009 and Jan 2 is the last work day of the new year.

 

[harm.charles]

Try this

=DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),2)+(week_num-1)*7+1

--
__________________________________
HTH

Bob




Thanks for the reply.
Week1 should be "Dec 29 - Jan 2".  29 is the 1 workday for week 1 of
the new year 2009 and Jan 2 is the last work day of the new year.

Bob,

Thanks for the formula. I'll give it a try, but I was really looking
for vba . Maybe with you formula I'll be able to work it out.

 

[Bob Phillips]

Function WeekDate(Week As Long)
'=DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),2)+(week_num-1)*7+1

WeekDate = DateSerial(Year(Date), 1, 1) - Weekday(DateSerial(Year(Date),
1, 1), vbMonday) _
+ (Week - 1) * 7 + 1

End Function


--
__________________________________
HTH

Bob

Try this

=DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),2)+(week_num-1)*7+1

--
__________________________________
HTH

Bob




Thanks for the reply.
Week1 should be "Dec 29 - Jan 2". 29 is the 1 workday for week 1 of
the new year 2009 and Jan 2 is the last work day of the new year.

Bob,

Thanks for the formula. I'll give it a try, but I was really looking
for vba . Maybe with you formula I'll be able to work it out.