week startdate and enddate based on week number

[aman]

hi guys

Can anyone please help me to find the week start date(monday) and weekend(sunday) using the week number. I am able to find the weeknumber using the code below but don't know how to find the start date and end date of the week.

Dim iNumberOfTheWeek As Integer
iNumberOfTheWeek = DatePart("ww", Now())

Regards
Aman

 

[pbaldy]

You should find functions here for first/last day of the week:

http://www.pacificdb.com.au/Support/code_datetime.htm

 

[Charli]

There is a weekday function

weekday(date, firstDayofWeek)
If firstDayofWeek is set to vbSunday, then the function returns 1 for sunday, 2 for monday, etc

So you could check what day of the week your given date was, then subtract or add the appropriate number to get your monday and sunday

 

[aman]

tried the formulas but could not figure it out. Can you please tell me what functions I have to use exactly.

Thanks

 

[pbaldy]

First day of a specified week (Monday = day 1)

myDate = dte - Weekday(dte, vbMonday) + 1
myDate = dateadd("d",1-weekday(dte,vbMonday),dte)

Last day of a specified week (Monday = day 1)

myDate = dte + (7 - Weekday(dte, vbMonday))
myDate = dateadd("d",7-weekday(dte,vbMonday),dte)

 

[aman]

Hi

Thanks for your reply. Could you please tell me what is dte here.Is it any date or week number.

If its date then can I write Date instead of dte so it will give me todays date.

Thanks
AMan

 

[pbaldy]

Yes, dte can be any date value passed to the formula, and you can certainly use the Date() function there. I'm surprised you didn't just try it.

 

[prley]

First day of a specified week (Monday = day 1)

myDate = dte - Weekday(dte, vbMonday) + 1
myDate = dateadd("d",1-weekday(dte,vbMonday),dte)

Last day of a specified week (Monday = day 1)

myDate = dte + (7 - Weekday(dte, vbMonday))
myDate = dateadd("d",7-weekday(dte,vbMonday),dte)

Sorry to bring an old thread back but as usual I found exactly what I needed in the above function to find the first day of the week for a given date. Thanks pbaldy!!! Works like a charm.

Otherwise I was going to subtract a number of day based on the day what the day of the week function returned. That would result in a long if statement.

 

[pbaldy]

Happy to help!