# Thread: Ascertain date that week 1 started for the current year

1. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316

## Unanswered: Ascertain date that week 1 started for the current year

I have been trying to work this one out all morning...

How can I ascertain the date of when week 1 started for any given year? So, let's say my input date is 7th Jan 2007, I would expect an output of 31st Dec 2006 (first DayOfWeek of first week of 2007). If my input is 5th Jan 2008, I would expect an output of 30th Dec 2007 (first DayOfWeek of first week of 2007). All this is based on the assumption that first DayOfWeek is a Sunday.

Any help much appreciated.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
So let me get this straight...
If 7th Jan 2007 was a sunday, you'd want to return Sunday 31st Dec 2006?
Kinda sounds like it might be easier to have a hard coded date set in a table...
but I'm sure r937 will tell you otherwise

It might also be useful to know this:
Weekday(date, [firstdayofweek])
you'd be wanting Weekday(date, acSunday)

This returns an integer value representing each day of the week.

Try simplifying your problem a wee bit... It's a bit of a mouthful. Break it down into processing steps.

- GeorgeV

3. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
So let me get this straight...
If 7th Jan 2007 was a sunday, you'd want to return Sunday 31st Dec 2006?
Yes! Even if the input date was 1st Jan 2007, I'd still want an result of 31st Dec 2006.

Kinda sounds like it might be easier to have a hard coded date set in a table...
I was thinking about this, but it didn't seem like an especially elegant solution. Still, it would keep things simple.

It might also be useful to know this:
Weekday(date, [firstdayofweek])
you'd be wanting Weekday(date, acSunday)

This returns an integer value representing each day of the week.
I'm already using something similar to that to ascertain the WeekOfYear of any given date.

Try simplifying your problem a wee bit... It's a bit of a mouthful. Break it down into processing steps.
I have, and I failed, which is why I posted here!

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
Haha ok!

So for ANY Sunday in 2007 you want to return the very last Sunday of 2006?
aand for ANY Sunday in 2008 you want to return the very last Sunday of 2007?

Do you want to do this for any given Monday, Tuesday, Weds... etc? Return the last one in the year before?

I imagine we can use the Weekday to find the day of the week and I'm almost certain there's a weeknumber function - combine the two and I reckon we have a solution!

What say you?

5. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Um ... This 1 is SOOOOOOO simple ... By your own reckoning, get the day of week for the 1st day of the desired calender year then subtract that number of days from that 1st day ... You might need to play with a +/- 1 offset to get the right day ... The DateAdd function just might come in handy

6. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Hi

Does this do what you want

Code:
```Function FirstSundayOfYear(ByVal ThisYear As Integer) As Date
Dim Firstday As Date

Firstday = (DateSerial(ThisYear, "01", "01"))

If Weekday(Firstday, vbSunday) <= 5 Then
FirstSundayOfYear = Firstday + (1 - Weekday(Firstday, vbSunday))
Else
FirstSundayOfYear = Firstday + (8 - Weekday(Firstday, vbSunday))
End If

End Function```
??

MTB

7. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
Originally Posted by georgev
So for ANY Sunday in 2007 you want to return the very last Sunday of 2006?
aand for ANY Sunday in 2008 you want to return the very last Sunday of 2007?
Yes, that is indeed what I need, although I didn't really visualise it until I just skipped through the Outlook calendar... I think I know how to do it now. Thanks for the help chaps.

8. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
Well, I now have another problem - my WeekNumber function isn't working as expected. I have tried 3 different WeekNumber functions found from various places on the web, and non of them will give me a result of 1 for a date of 1st Jan 2006.

Here is the first one I was using:

Code:
```Function fWeekNumber(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
fWeekNumber = 0
If InputDate < 1 Then Exit Function
A = Weekday(InputDate, vbSunday)
B = Year(InputDate + ((8 - A) Mod 7) - 3)
C = DateSerial(B, 1, 1)
D = (Weekday(C, vbSunday) + 1) Mod 7
fWeekNumber = Int((InputDate - C - 3 + D) / 7) + 1
End Function```
This looked ideal as it apparently enables you to set when a week should start. However, when you pass a date of 1st Jan 2006 (which is a Sunday), it returns a value of 52, which is clearly wrong, as it is the first week of the new year.
Last edited by bcass; 02-14-07 at 12:44.

9. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
I stumbled across vbFirstWeekOfYear in access help...
Might be worth a look in

10. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Hi again

How about

Code:
```Function WkNo(ByVal Thisdate As Date) As Integer
Dim Firstday As Date
Dim FirstSundayOfYear As Date

Firstday = (DateSerial(Year(Thisdate), "01", "01"))

If Weekday(Firstday, vbSunday) <= 5 Then
FirstSundayOfYear = Firstday + (1 - Weekday(Firstday, vbSunday))
Else
FirstSundayOfYear = Firstday + (8 - Weekday(Firstday, vbSunday))
End If

WkNo = Int((Thisdate - FirstSundayOfYear + 7) / 7)
End Function```

??

MTB

11. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Hi

Rev B

This should take care of all the end condition ie week 53 etc.

Code:
```Function WkNo(ByVal Thisdate As Date) As Integer
Dim CalcYear As Integer
Dim Firstday As Date
Dim FirstSundayOfYear As Date

CalcYear = Year(Thisdate) + 1
Do
Firstday = (DateSerial(CalcYear, 1, 1))

If Weekday(Firstday, vbSunday) <= 5 Then
FirstSundayOfYear = Firstday + (1 - Weekday(Firstday, vbSunday))
Else
FirstSundayOfYear = Firstday + (8 - Weekday(Firstday, vbSunday))
End If

CalcYear = CalcYear - 1
Loop Until FirstSundayOfYear <= Thisdate

WkNo = Int((Thisdate - FirstSundayOfYear + 7) / 7)
End Function```

At least I think so !!

Some one will let me know if not?

MTB

ps isn't i more usual to have Monday as the first day of the week for week numbering ?
Last edited by MikeTheBike; 02-15-07 at 04:28.

12. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
Cheers. That worked perfectly. Not sure about Monday being the norm for first day of week. Isn't the deafult WorkDay set to vbSunday in VBA?

13. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
Originally Posted by bcass
Cheers. That worked perfectly. Not sure about Monday being the norm for first day of week. Isn't the deafult WorkDay set to vbSunday in VBA?
Correct - sunday is the default

14. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Yes Sunday is the Default, but that doesn't prove anything (this is microsoft after all).

All the desk diaries I ever seen in the last 30 years have Monday as the first week day, and weeks are numbered accordingly !?

MTB
Last edited by MikeTheBike; 02-15-07 at 05:43.

#### Posting Permissions

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