Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Week Number of Date

1. Member
Join Date
28th February 2005
Posts
72

## Week Number of Date

Hello again,

I'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end-date of a week (from monday till sunday)
the only variable that I wanna give is the Weeknumber.

So if I write a weeknumber in cell(a,1)
I want the begin-date (monday) in cell (b,1) and
I want the end-date (sunday) in cell (c,1).

for example:
A B C
1 36
2 04-09-2006
3 10-09-2006
4

can anybody help me with this??

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: dates by weeknumber

Before proceeding, I think you should reach this article on weeknumbers in excel. It's pretty comprehensive.

Then if you could decide which type of week numbering you are using we can probably come up with a solution.

3. ## Re: dates by weeknumber

Personally I use the functions written by John Green

VB:
``` 'Functions written by John Green
Public Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If

End Function

Public Function WeekStart(WhichWeek As Integer, WhichYear As _
Integer) As Date

WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)

End Function

```
Assume you have 2006 in B2
You have weeknumbers 1 to 53 in A4 down

in B4 =weekstart(A4,\$B\$1)

returns 02 Jan 2006

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: dates by weeknumber

Hi,

Something this will works for current year(2006)

In A2,

=DATE(2006,1,1)-WEEKDAY(DATE(2006,1,1))+A1*7-5

In A3,

=A2+6

HTH

5. Member
Join Date
28th February 2005
Posts
72

## Re: dates by weeknumber

thanx Krishnakumar...

that does the trick that I wanted..

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno