Access Question : Week Number

[Zahed]

Can anyone help. Need to calculate Week number of a date. But Week must start on a Monday not a Sunday.

Excel provides a function where

WeekNum("Date", 1) starts week on Sunday
WeekNum("Date", 2) starts week on Moday

many thanks

Zahed

[CaptainD]

Check out Weekday()

From access help:

WEEKDAY
Returns a number from 1 to 7 that identifies the day of the week, based on a given number that represents a date.

Syntax

WEEKDAY(serial_ number,return_t ype)

Serial_number is a number that represents a date or text in date format, such as "4-1-1999" or "31-Oct-2000".

Return_type is a number: for Sunday = 1 through Saturday = 7, use 1; for Monday = 1 through Sunday = 7, use 2; for Monday = 0 through Sunday = 1, use 3.

[comteck]

Declare your week as an array:
Dim WeekNum(7) as String

Then assign a number to each:
WeekNum(0) = "Monday"
WeekNum(1) = "Tuesday"
etc.

[COE201]

Greeting:

Any hint please on how to use the MS Excel Weeknum such that:

 First working day is Saturday
 Years are 52 weeks
 Specify holiday days by the user

Appreciations your reply and help

Thank you and Best regards

COE201

[missinglinq]

Zahed wants the week number, not the weekday number!

In Access VBA, with Monday as the first day of the week:

WeekNumber = Format(YourDate , "ww",vbMond ay)

Welcome to TheScripts!

Linq ;0)>

[Whasigga]

Is there a way to do the reverse? Such as you have the week number, but want to find the date of the first day in that week? As an example, if using Monday as the first day of the week, what is the date of the first day of week 6 for a given year.

[missinglinq]

This isn't simple, and I'm sure someone will come along with a two line answer, but I think this works.

Assuming that

TargetWeek holds the Week of the Year

TargetYear holds the Year in question

FirstOfWeekForG ivenYear holds the answer

[CODE=vb]Private Sub CalcWeekOfYearS tartDate_Click( )
'Given Week Of Year where FirstDayOfWeek is 'Monday Calculates First Day of Given Week of Year

FirstOfYear = "1/1/" & TargetYear

InterimDate = DateAdd("ww", TargetWeek - 1, FirstOfYear)

FirstDayOfTarge tWeek = (InterimDate - (Weekday(Interi mDate, vbMonday) - 1))

Me.FirstOfWeekF orGivenYear = FirstDayOfTarge tWeek

End Sub[/CODE]

Linq ;0)>

[Whasigga]

cool. that was shorter than my solution. Although I did have to add a check because apparently as is, it bases Week 1 off of January 1. Unfortunately my company doesn't do that. If Jan 1 is on Saturday or Sunday it is not included in Week 1. So I had to add a check for that, but this helps...thanks!

[missinglinq]

Glad we could help!

Linq ;0)>

[COE201]

Many thank for the help; I have the following:

First date is converted to be a Monday since that may not be the case from pre-entered dates by:

=IF(WEEKDAY(F1; 2)=1;F1;F1-WEEKDAY(F1;2)+1 )

Any way please to convert this expression so that it will start on Saturday instead

Thank you and Best regards

[missinglinq]

COE201, you appear to be working in Excel rather than Access VBA; is this correct?

Linq ;0)>

[COE201]

Yes missinglinq and thank you for the help in previous post of:

WeekNumber = Format(YourDate , "ww",vbMond ay); it also work in MS Excel by placing vbSaturday or any day instead.

Best regards,

[neilramirez084]

WeekNumber = Format(YourDate , "ww",vbMond ay);
this one works..

[missinglinq]

I'm glad it works for you in Excel, COE201! Just be aware that many, many things aren't transferrable form Access to Excel!

WeekNumber = Format(YourDate , "ww",vbMond ay);
this one works..

neilramirez084, what is your point in posting code that's identical to that which has already been posted in this thread twice, including the post immediately prior to yours?

Linq ;0)>