weeknumber

[John Britto]

Dear All,
Good day,
I am using weeknum() function to get week number. But, there is a
difference between the computer generated weeknum and what is shown on a real
calendar. Example, today is week 41 by weeknum(), but the calendar shows
week 40 (3 ~ 9 Oct). Why? Please...

On excel, if I enter a number on a cell it should calculate the product,
i.e. in a cell the value is 1000 (constant) and when I enter 5 it should
should automatically give 5000, if i enter 10, the result must be 10,000..
Is there any function or any manipulation method please...thanks

[Norman Jones]

Hi John,

> Why? Please...

See:
http://tinyurl.com/dvavp


---
Regards,
Norman



"John Britto" <[email protected]> wrote in message
news:[email protected]...
> Dear All,
> Good day,
> I am using weeknum() function to get week number. But, there is a
> difference between the computer generated weeknum and what is shown on a
> real
> calendar. Example, today is week 41 by weeknum(), but the calendar shows
> week 40 (3 ~ 9 Oct). Why? Please...
>
> On excel, if I enter a number on a cell it should calculate the product,
> i.e. in a cell the value is 1000 (constant) and when I enter 5 it should
> should automatically give 5000, if i enter 10, the result must be 10,000..
> Is there any function or any manipulation method please...thanks

[Norman Jones]

Hi John,

I omitted to add, see particularly the post from Norman Harker.

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi John,
>
>> Why? Please...
>
> See:
> http://tinyurl.com/dvavp
>
>
> ---
> Regards,
> Norman
>
>
>
> "John Britto" <[email protected]> wrote in message
> news:[email protected]...
>> Dear All,
>> Good day,
>> I am using weeknum() function to get week number. But, there is a
>> difference between the computer generated weeknum and what is shown on a
>> real
>> calendar. Example, today is week 41 by weeknum(), but the calendar shows
>> week 40 (3 ~ 9 Oct). Why? Please...
>>
>> On excel, if I enter a number on a cell it should calculate the product,
>> i.e. in a cell the value is 1000 (constant) and when I enter 5 it should
>> should automatically give 5000, if i enter 10, the result must be
>> 10,000..
>> Is there any function or any manipulation method please...thanks
>
>

[nsv]

Look in a US calender and you will see that this week has number 41. The americans count the weeks differently from the rest of the world, and Microsoft is an american thing.


nsv

[Arvi Laanemets]

Hi

MS Weeknum ?!:-)
The function with 2nd parameter as 1 (US week system) returns week 53 for
dates 26. - 31.12.1999 (6 days) and week 1 for date 01.12.2000 (1 day). The
date 02.01.2000 belongs to week 2. With 2nd parameter set to 2 we get 5-day
week and 2-day week in time interval 27.12.1999 - 02.01.2000

What a crap! A week is by definition 7 days long! And doesn't have any
connection to year, we use, at all. I think it originates from celestial
calendar probably.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"John Britto" <[email protected]> wrote in message
news:[email protected]...
> Dear All,
> Good day,
> I am using weeknum() function to get week number. But, there is a
> difference between the computer generated weeknum and what is shown on a
> real
> calendar. Example, today is week 41 by weeknum(), but the calendar shows
> week 40 (3 ~ 9 Oct). Why? Please...
>
> On excel, if I enter a number on a cell it should calculate the product,
> i.e. in a cell the value is 1000 (constant) and when I enter 5 it should
> should automatically give 5000, if i enter 10, the result must be 10,000..
> Is there any function or any manipulation method please...thanks

[Ron Rosenfeld]

On Thu, 6 Oct 2005 22:25:02 -0700, "John Britto"
<[email protected]> wrote:

>Dear All,
>Good day,
>I am using weeknum() function to get week number. But, there is a
>difference between the computer generated weeknum and what is shown on a real
>calendar. Example, today is week 41 by weeknum(), but the calendar shows
>week 40 (3 ~ 9 Oct). Why? Please...

Because your calendar and the Excel WEEKNUM worksheet function are using
different definitions of WEEKNUM.

You can find in HELP how Excel calculates week number.

Your calendar is most likely using the ISO standard, and you can find that
definition on the WEB using Google to search for ISO Weeknumber.

Here is a UDF to calculate the ISO Weeknumber. To enter it, <alt><F11> opens
the VB Editor. Ensure your project is highlighted in the Project Explorer
window, then Insert/Module and paste the code below into the window that opens.

To use the function, enter =ISOWEEKNUM(dt) in some cell where dt is either a
date constructed according to Excel rules, or a cell reference that contains a
date.

===================================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
====================================


>
>On excel, if I enter a number on a cell it should calculate the product,
>i.e. in a cell the value is 1000 (constant) and when I enter 5 it should
>should automatically give 5000, if i enter 10, the result must be 10,000..
>Is there any function or any manipulation method please...thanks

You could do this using a VBA event driven macro. Although I think that
separate data input and output cells would be simpler.

Here is a set of routines that should do something similar to what you describe
for cell A1. It ignores Zeros and Text.

To enter this, right click on the worksheet tab and select View Code. Paste
the code below into the window that opens. To use it, enter numeric data into
A1.

===============================
Option Explicit
Dim AOI As Range
Dim CellVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count <> 1 Then GoTo X
If Not Intersect(Target, AOI) Is Nothing Then
With Target
If CellVal = 0 Then GoTo X
If IsNumeric(CellVal) Then Target.Value = Target.Value * CellVal
End With
End If
X: Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set AOI = [A1]
If Target.Count <> 1 Then Exit Sub

If Not Intersect(Target, AOI) Is Nothing Then
CellVal = Target.Value
End If
End Sub
==================================


--ron

[Ron de Bruin]

See also
http://www.rondebruin.nl/weeknumber.htm

And
http://www.rondebruin.nl/isodate.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"nsv" <[email protected]> wrote in message news:[email protected]...
>
> Look in a US calender and you will see that this week has number 41. The
> americans count the weeks differently from the rest of the world, and
> Microsoft is an american thing.
>
>
> nsv
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=474032
>