Home |
Search |
Today's Posts |
#1
|
|||
|
|||
weeknumber
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 |
#2
|
|||
|
|||
Hi John,
Why? Please... See: http://tinyurl.com/dvavp --- Regards, Norman "John Britto" wrote in message ... 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 |
#3
|
|||
|
|||
Hi John,
I omitted to add, see particularly the post from Norman Harker. --- Regards, Norman "Norman Jones" wrote in message ... Hi John, Why? Please... See: http://tinyurl.com/dvavp --- Regards, Norman "John Britto" wrote in message ... 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 |
#4
|
|||
|
|||
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<attarkon.ee ) "John Britto" wrote in message ... 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
On Thu, 6 Oct 2005 22:25:02 -0700, "John Britto"
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 |
#7
|
|||
|
|||
See also
http://www.rondebruin.nl/weeknumber.htm And http://www.rondebruin.nl/isodate.htm -- Regards Ron de Bruin http://www.rondebruin.nl "nsv" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to get the current weeknumber | Excel Discussion (Misc queries) | |||
Weeknumber in Excel | Excel Discussion (Misc queries) | |||
Wrong weeknumber 2004 / 2005 | Excel Discussion (Misc queries) |