Week 53 and week 1, 2010

[rogerarce]

Hello friends: I made an order entry application 2 years ago. The orders ship by week number. The problem is the following:
If I enter 12/31/2009 happens to be week 53 but if I enter 01/01/2010 happens to be week 1? I have 2 printer calendars that show week 53 from 12/12/2009 to 01/01/2010. This is a big problem when entering the orders. Any help will be appreciated.

Thanks!

code: date has WW mask.

 

[kzutter]

How are you determining the week number?
Perhaps if you share the code, we can help you better.

Are you using the function DatePart?
It has an optional parameter "firstweekofyear", which can be one of four values.

I assume you are using the default value of vbFirstJan1 which gives the results you stated, weeks 53 and 1.

Setting it to vbFirstFourDays will put both 12/31/2009 and 01/01/2010 in the same week. However it returns week #52, since it shifts the starting week for 2009.

Looks like you will have to do a work-around for now and then decide if you need to change the coding for the future.

-Ken

 

[alvechurchdata]

I think you need to ask the business people about this. Do they want to consider an order placed on Fiday Jan 1st as being in the last week of 2009 or the first week of 2010?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MajP]

My assumption is that the calculation is based on what year you are concerned about.
I think if you are building your 2009 calendar then 1/1/2010 (Friday) is in week 53, but if you are working on your 2010 calendar then it is in week 1.

If that is the case whatever function you build, you are going to have to provide the year in question for context.

So you probably have some function like

Public Function getShipWeek(dtmShipDate As Date, shippingYear as integer) As Integer

...some code
if month(shipDate) = 1 and year(shipDate) = shippingYear + 1 then
determine if it is in the 53rd week using "inLastWeek"
...some code
else
what Ken is suggesting
end if
End Function

Here is a function to determine if it is in the 53rd week.

Public Function inLastWeek(dtmShipDate As Date, shippingYear As Long) As Boolean
  Dim lastDayOfYear As Date
  Dim tempDate As Date
  lastDayOfYear = DateSerial(shippingYear, 12, 31)
  tempDate = lastDayOfYear
  Do
    tempDate = tempDate + 1
    If dtmShipDate = tempDate Then
      inLastWeek = True
      Exit Function
    End If
  Loop Until Weekday(tempDate) = vbSunday
End Function

 

[SkipVought]

Hi,

In the aerospace industry, companies have a manufacturing day calendar, that contains accounting period information. This removes any ambiguity that comes with date calculations and they are legion.

That would be my suggestion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Here would be the complete code

Public Function getShipWeek(dtmShipDate As Date, shippingYear As Integer) As Integer
   Dim lastDayOfYear As Date
   lastDayOfYear = DateSerial(shippingYear, 12, 31)
   'verify the shipping date is in the current year or the next year
   If Year(dtmShipDate) < shippingYear Then
      Exit Function
   End If
   'If it is in the next year verify if falls in the last shipping week
   If dtmShipDate > lastDayOfYear Then
     If inLastWeek(dtmShipDate, shippingYear) Then
       getShipWeek = getShipWeek(lastDayOfYear, shippingYear)
     End If
   Else
     getShipWeek = DatePart("ww", dtmShipDate, vbMonday, vbFirstJan1)
   End If
End Function

Public Function inLastWeek(dtmShipDate As Date, shippingYear As Integer) As Boolean
  Dim lastDayOfYear As Date
  Dim tempDate As Date
  lastDayOfYear = DateSerial(shippingYear, 12, 31)
  tempDate = lastDayOfYear
  Do
    tempDate = tempDate + 1
    If dtmShipDate = tempDate Then
      inLastWeek = True
      Exit Function
    End If
  Loop Until Weekday(tempDate) = vbSunday
End Function
[code]

Verification
Building the 2009 calendar
?getShipweek(#1/1/2009#,2009)
 1 
?getShipweek(#1/1/2010#,2009)
 53 
building the 2010 calendar
?getShipweek(#1/1/2010#,2010)
 1 

Error checking.  Passing a 2009 date when building the 2011 calendar
?getShipweek(#1/1/2009#,2011)
 0 

You may also want to play with the DatePartfunction.  This assumes the first week of the year start wherever Jan 1 falls.  But if Jan 1 falls on a Sunday and a week goes Monday to Sunday would you want week 1 to be that one day week or the next week?

 

[rogerarce]

The code i use in my form is very easy and simply. I have one field with
date format.

field [Date]with date format: date 01/04/2010

I have one more field [week] with WW format

=[date]

Then when you type the date on field [date] it shows

the week number in field [week]

Ej. [01/04/2010] = [2]

The application shows 01/04/2010 as week 2 but if you see a
printed calendar, week 2 is 01/11/2010. The problem could be that the customer could receive an order with 1 week in advance. What is wrong with this? We sell plants and sometimes nurseries are full and can not receive plants in advance.

Thank you for all the answers you have posted. Please see the attached image

 

[MajP]

This is why you need to define the start of the week and the first week of the year based on your buisness rules:

The week where jan 1 resides
or
The first full week
or
The first four days

You also will probably have to define which day your week starts.

01/04/2010 is in fact in week 2 (based on the first week of the year being the week where 1/1 resides)

It is only in the first week if you define the first week being the first full week or first 4 day week.

Look at all the parameters of the format function or datepart function and you will see there is a firstdayofweek, and firstweekofyear. If you leave these blank they default to Sunday and the week where Jan 1 resides.

 

[MajP]

But again that does not solve the issue of a date falling into the next January unless you pass to the function the base year. Jan1 2010 falls into the 53rd shipping week of 2009 (and/or the first of 2010), but in order to make that determination you need to do what I did and pass in the base year.

 

[rogerarce]

Hello Majp: I´ll work with the code you pass.

Thanks for your help.

Regards

 

[hermanlaksko]

Dont know if you allready solved this problem a long time ago, but here is my 5 cent's worth.

As I see it you use format "WW" for your field, you can use 2 formats:

1. Format([YrField],'ww',0,0) - The first 0 is for first day of the week 0 indicates, Use systems settings. Second 0 is for first week of the year, again it stands for use system settings.

You can, however, force your program by using 2 instead of 0:

2. Format([YrField],'ww',2,2) - The first 2 is for first day of the week, 2 indicates your week starts on a monday. Second 2 is for first week of the year, start with the first week that has at least four days in the year.


Herman
Say no to macros

 

[avi951]

Hello, I have the same problem within the form.
In form I have a date with default value 'Date()' which is formatted as week ('ww')showing week numbers.

It was ok for the previous year but now it reads date Jan, 4th as week No 2 while I need it to be No 1.

Please help.

 

[SkipVought]

Run this procedure and look at the results in the Immediate Window. It shows you exactly what is happening with Format(Date(), "ww").

The week result is context sensative, as you can see with respect to week 53 (in context with 2009) and week 1 (in context with 2010).

Sub test()
    Dim d As Date
    For d = Date - 30 To Date + 30
        Debug.Print d, Format(d, "ww")
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hermanlaksko]

The "formating" of the format cmd (vbmonday,vbFirstFourDays) ie: Format(Yr!Field, "MM-DD-YYYY",vbMonday ,vbFirstFourDays )
will give you correct first day of your week AND correct first week of any given year.

If one lives out side the US, and most of us do, one should incl. these parameters on the format cmd.

Best wished for the season
Herman

Herman
Say no to macros

 

[avi951]

Well thanks guys for your replies but still it ain't clear to me.

Can I accomplish correct WW within the form by adjusting properties or do you mean some hard coding is needed (eg. Herman's "format cmd (vbmonday,vbFirstFourDays)..." - where do I enter this? please clarify, TIA

 

[SkipVought]

Check out VB Help on Format. Its all in there.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hermanlaksko]

Format(Date, "WW", 0, 0)

Herman
Say no to macros

 

[SkipVought]

I think that you meant

Format(Date, "WW", 2, 2)

which yeilds...
[tt]
12/28/2009 53
12/29/2009 53
12/30/2009 53
12/31/2009 53
1/1/2010 53
1/2/2010 53
1/3/2010 53
1/4/2010 1
1/5/2010 1
1/6/2010 1
1/7/2010 1
1/8/2010 1
1/9/2010 1
1/10/2010 1
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hermanlaksko]

No I did not, Skip, but thanks for putting the spotlight on this point.

You can use 2,2 but if your system is set up correctly

0 will mean vbUseSystem
and the second
0 will mean vbUseSystem (also)

However using 2,2 as you suggest, will force your user to have vbmonday as first day of week and vbfirstfourdays as first week of the year. Result being that outside your country, ie. in the US, the user will forced to use this "wrong" setting.
The 0,0 solution is therefore the corect solution - Unless you want to force your program, then your 2,2 solution is - ofcourse, correct.

Herman
Say no to macros

 

[avi951]

I just got perfect answer from Michiel:
------------
I suppose that when you use
=Format(Date();"ww";0;3)
as ControlSource for the field, you'll be a happy man.
You can leave the 'Format' and 'DefaultValue' of the field empty.

hope this helps
Michiel

> Hello,
> I have a problem within the form where I have date with default value
> 'Date()' which is formatted as week ('ww') showing week number.
> It was ok for the previous year but now it reads date Jan, 4th as week
> No 2 while I need it to be No 1.
> Is there a way to fix this in form / format property only or some hard
> coding is necessary?
> Please help.
> TIA