 |
If you are interested in playing poker on the internet, I recomend Everest Poker. By signing up with Everest Poker you get an USD 100 welcome bonus. I highly recommends this site where there is an excellent selection of tables and an unusually high proportion of fish (weak players)! |
|---|---|
|
|
|
Excel with ProbabilityOf.com - Generic Calendar - Week numbering - Translations - Tips & Tricks
This page contains some usefull information about Microsoft Excel 2000 2002 2003.
Content
- Information about Excel
- Usefull Excel files and functions
- Norwegian(norsk) and Swedish(svenska) translations of Excel functions
- Wishes for next release of Office
Please feel free to contact me on email at Nikolai Sandved (nsaa@ProbabilityOf.org)
Information about Excel
In this section I will add information about Excel that is not so commonly known, but may affect many users
Excel treat 1900 as a leap year
Excel treats 1900 as a leap year (http://www.ozgrid.com/Excel/ExcelDateandTimes.htm), but this is NOT correct.
Here's the gregorian calendar rule for when a
year is a leap year according to Wikipedia and timeanddate.com
In the Gregorian calendar, ... ... ,the following rules decides which years are leap years: 1. Every year divisible by 4 is a leap year. 2. But every year divisible by 100 is NOT a leap year 3. Unless the year is also divisible by 400, then it is still a leap year. This means that year 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years , while year 2000 and 2400 are leap years.
See http://en.wikipedia.org/wiki/Leap_year and http://www.timeanddate.com/date/leapyear.html
See also this article by Microsoft http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q214326&ID=KB;EN-US;Q214326.
Excel do not password protect sheets properly
If you protect some areas of an excel file (tested on Excel 2000 and 2002) you have no problem breaking the password. Only add this VBA CODE and run it from the sheet you want to unprotect:
Sub PasswordBreaker()
'Author unknown
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
ActiveWorkbook.Sheets(1).Select
Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
|
Example: In the Euro 2004 Competition, the official website promoted an Excel sheet named http://www.euro2004.com/MultimediaFiles/Predictor/EURO2004_predictor_1.1.0_LockedCells_links.xls . Here's a lot of (hidden) sheets etc. and they're password protected. But by running the code over you get access to all the password protected areas.
Excel treats weeks in a strange way
The Excel function WEEKNUM(serial_number,return_type) do not return week numbers according to international standards (ISO 8601). Microsoft says very ignorant that ISO 8601 '...is a European standard that defines the first week as the one with the majority of days (four or more...'. This is an international standard! Microsoft, Please feel free to check out these standards # USA Standard: ANSI X3.30-1985(R1991) and # USA Standard: NIST FIPS 4-1 which have implemented ISO 8601. A long list of Implementation of the ISO 8601 Standard Around The World. What the #¤% is so European about this?
For example will the date 2003-12-29 (=WEEKNUM(37984,2)) return week 53. This is not the case since this is Week 1 next year (2004-W01-1).
Examples (Bold week numbers is not correct): Date Correct weeknumber WEEKNUM(date;2) 2002-12-28 2002-W52 52 2002-12-29 2002-W52 52 2002-12-30 2003-W01 53 2002-12-31 2003-W01 53 2003-01-01 2003-W01 1 2003-01-02 2003-W01 1 ... 2003-12-28 2003-W52 52 2003-12-29 2004-W01 53 2003-12-30 2004-W01 53 2003-12-31 2004-W01 53 2004-01-01 2004-W01 1 ... 2004-12-31 2004-W53 53 2005-01-01 2004-W53 1 2005-01-02 2004-W53 1 2005-01-03 2005-W01 2 2005-01-04 2005-W01 2
As you can see here Excel treats 1. january as week 1. This is NOT according to international standards! They even set sunday as the first day of the week as default (The second argument to WEEKNUM says 1= sunday as first day(default), and 2 monday as first day). Why do the Microsoft developers have so great difficulties doing things correctly?
Here's a good treatment of the different issues around the Excel week subject http://www.cpearson.com/excel/weeknum.htm
My own PHP implemented Calendar calendar.php
The correct week numbering rules can be summarized in these 3 rules:
- Rule 1 January 4th. is always in week 01
- Rule 2 Always 52 or 53(leap week) ISO week in a given year: "A year has a leap week if and only if the corresponding Gregorian year begins on a Thursday or is a leap year begining on a Wednesday or a Thursday. By definition, its new year varies just 6 days against the Gregorian Calendar (3 days early to 3 days late)."
- Rule 3 A ISO week start at a Monday(1) and ends at a Sunday (7)
See the ISO_8601_Date-Week-Date.xls(
) sheet or the Calendar Sheet (Excel_Calendar.xls
) for a example of a correct implemented
Date to week function (named =DateToWeek) and a week
to date function (=WeekToDate). The function follows here:
Date to week function
Public Function DateToWeek(ByVal datDate As Date, _
Optional ByVal bytTruncFormat As Byte = 0, _
Optional ByVal bytShortLongFormat As Byte = 0) As String
'******************************************************************************
'
' Macro created 2003-03-18 by Nikolai Sandved (nsaa@pvv.org)
'
' Description: This Function return the ISO8601 week
' This code appears on the site
' http://www.probabilityof.com/ISO8601.shtml
' http://www.probabilityof.com/excel.shtml
'
' The week calculations follow the ISO 8601 standard
' http://dmoz.org/Science/Reference/Standards/Individual_Standards/ISO_8601/
'
'
' Input:
' datDate - Microsoft Excel date
' Optional
' bytShortLongFormat 0 - Long format(default) : YYYY-Www-D
' 1 - Short format : YYYYWwwD
' bytTruncFormat 0 - Year, week and day(default) : YYYY-Www-D/YYYYWwwD
' 1 - Year and week : YYYY-Www/YYYYWww
' 2 - Year : YYYY
' 3 - Week and day : Www-D/WwwD
' 4 - Week : Www
' 5 - Only Week : ww
' Output:
' DateToWeek - A string following the pattern "YYYY-Www-D" (Default)
'
' Same calculations in Excel can be done like this (in US change ; to ,):
' Cell(A1) =date in Excel
' Cell(B1) =RIGHT("0" & 1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6));1;5)
' + WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6));1;3)))/7);2)
' Cell(C1) =IF(AND(MONTH(A1)=12;B1="01");
' YEAR(A1)+1;
' IF(AND(MONTH(A1)=1;OR(B1="52";B1="53"));
' YEAR(A1)-1;
' YEAR(A1)))
' &"-W" & B1 & "-" & WEEKDAY(A1;2)
'
'
'The following two rules applies when converting a gregorian date to the ISO week
'and vice versa
'Rule 1 January 4th. is always in week 01
'Rule 2 Always 52 or 53(leap week) ISO week in a given year: "A year has a leap
' week if and only if the corresponding Gregorian year begins on a Thursday or
' is a leap year begining on a Wednesday or a Thursday. By definition, its new
' year varies just 6 days against the Gregorian Calendar (3 days early to 3
' days late)."
'Rule 3 A ISO week start at a Monday(1) and ends at a Sunday (7)
'From http://serendipity.magnet.ch/hermetic/cal_stud/palmen/lweek1.htm
'
'Example rule 1
' January 4th. 1993 is on a Monday(1993-01-04)
' 1993-01-03 -> 1992-W53-7
' 1993-01-04 -> 1993-W01-1
' January 4th. 1998 is on a Sunday (1998-01-04)
' 1997-12-28 -> 1997-W52-7
' 1997-12-29 -> 1998-W01-1
' 1998-01-04 -> 1998-W01-7
'Example rule 2
' Year start at a Thursday
' 1998-01-01 -> 1998-W01-4 (i.e a Thursday)
' 1998-12-31 -> 1998-W53-4 (->Leap week!)
' Year start at a Wednesday and is a Leap year
' 1992-01-01 -> 1992-W01-3 (i.e a Wednesday)
' 1992-02-29 -> 1992-W09-6 (and a Leap Year)
' 1992-12-31 -> 1992-W53-4 (->Leap week!)
' Year start at a Wednesday and is NOT a Leap year
' 1975-01-01 -> 1975-W01-3 (i.e a Wednesday)
' 1975-02-29 -> ERROR, No date, No Leap year
' 1975-12-28 -> 1975-W52-7
' 1975-12-29 -> 1976-W01-1 (No Leap week in 1975)
'
' Keyboard Shortcut:
'
'******************************************************************************
'** Error Control
On Error GoTo ErrorHandle
' ** Define variables
Dim byteWeekNumber As Byte 'The weeknumber (Between 1 and 53)
Dim strWeekNumber As String 'The weeknumber (Between 01 and 53)
Dim intWeekYear As Integer 'The weeknumberyear ()
Dim strShortLongFormat As String 'If long then "-"
'Calculates the weeknumber
'From http://www.cpearson.com/excel/weeknum.htm
'=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+
' WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)
byteWeekNumber = 1 + Int((datDate - DateSerial(Year(datDate + 4 _
- Weekday(datDate + 6)), 1, 5) + Weekday(DateSerial(Year(datDate + 4 _
- Weekday(datDate + 6)), 1, 3))) / 7)
'Adds leading 0 to weeknumbers less than 10
strWeekNumber = Right$("0" & byteWeekNumber, 2)
'Calculates the correct week year if necesarry
If bytTruncFormat < 3 Then
'If weekyear is one year ahead
If (Month(datDate) = 12 And strWeekNumber = "01") Then
intWeekYear = Year(datDate) + 1
'If weekyear is one year after
ElseIf (Month(datDate) = 1 And (strWeekNumber = "52" _
Or strWeekNumber = "53")) Then
intWeekYear = Year(datDate) - 1
'Same year
Else
intWeekYear = Year(datDate)
End If
End If 'bytTruncFormat < 3
' If long format add "-"
If bytShortLongFormat = 1 Then
strShortLongFormat = ""
Else
strShortLongFormat = "-"
End If
'Selects correct truncated format
Select Case bytTruncFormat
Case 0
DateToWeek = CStr(intWeekYear) & strShortLongFormat & "W" _
& strWeekNumber & strShortLongFormat _
& Weekday(datDate, vbMonday)
Case 1
DateToWeek = CStr(intWeekYear) & strShortLongFormat & "W" _
& strWeekNumber
Case 2
DateToWeek = CStr(intWeekYear)
Case 3
DateToWeek = "W" & strWeekNumber & strShortLongFormat _
& Weekday(datDate, vbMonday)
Case 4
DateToWeek = "W" & strWeekNumber
Case 5
DateToWeek = strWeekNumber
Case Else
DateToWeek = CStr(intWeekYear) & strShortLongFormat & "W" _
& strWeekNumber & strShortLongFormat _
& Weekday(datDate, vbMonday)
End Select
Exit Function
ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$)
End Function
|
Leap year and week to date function
Public Function WeekToDate(ByVal strWeek As String) As Date
'******************************************************************************
'
' Macro created 2001-01-18 by Nikolai Sandved (nsaa@pvv.org
'
' Description: This Function return the date
' This code appears on the site
' http://www.probabilityof.com/ISO8601.shtml
' http://www.probabilityof.com/excel.shtml
'
' The week calculations follow the ISO 8601 standard
' http://dmoz.org/Science/Reference/Standards/Individual_Standards/ISO_8601/
'
' Input:
' strWeek - Week given in either "YYYY-Www-D" or "YYYYWwwD" format
'
' Output
' WeekToDate - A date in Excel date format
'
' Same calculations in Excel can be done like this (in US change ; to ,):
' Cell(A1) =Week in Excel (ex.:1992-W09-6)
' Cell(B1) =IF(MOD(VALUE(LEFT(A1;4));400);IF(MOD(VALUE(LEFT(A1;4));100);
' IF(MOD(VALUE(LEFT(A1;4));4);0;1);0);1)
' Cell(C1) =(VALUE(MID(A1;7;2))-1)*7+VALUE(MID(A1;10;1))
' -WEEKDAY(DATE(VALUE(LEFT(A1;4));1;4);2)
' Cell(D1) =IF(E1="june";IF(C1<(178+B1);DATE(VALUE(LEFT(A1;4));6;C1-147-B1);
' IF(C1<(209+B1);DATE(VALUE(LEFT(A1;4));7;C1-177-B1);
' IF(C1<(240+B1);DATE(VALUE(LEFT(A1;4));8;C1-208-B1);
' IF(C1<(270+B1);DATE(VALUE(LEFT(A1;4));9;C1-239-B1);"oct"))));E1)
' Cell(E1) =IF(C1<28;DATE(VALUE(LEFT(A1;4));1;C1+4);IF(C1<(56+B1);
' DATE(VALUE(LEFT(A1;4));2;C1-27);IF(C1<(87+B1);
' DATE(VALUE(LEFT(A1;4));3;C1-55-B1);IF(C1<(117+B1);
' DATE(VALUE(LEFT(A1;4));4;C1-86-B1);IF(C1<(148+B1);
' DATE(VALUE(LEFT(A1;4));5;C1-116-B1);"june")))))
' Cell(F1) =IF(D1="oct";IF(C1<(301+B1);DATE(VALUE(LEFT(A1;4));10;C1-269-B1);
' IF(C1<(331+B1);DATE(VALUE(LEFT(A1;4));11;C1-300-B1);
' DATE(VALUE(LEFT(A1;4));12;C1-330-B1)));D1)
'
'
'******************************************************************************
'** Error Control
On Error GoTo ErrorHandle
'** Define Variables
Dim intYYYY As Integer 'Year part
Dim intWww As Integer 'Week part
Dim intDD As Integer 'Weekday part
Dim intYYYYILY As Integer '1 if intYYYY is Leap Year, else 0
Dim datYYYY0104 As Date 'The date YYYY-01-04 as date
Dim intYYYY0104Weekday As Integer 'The Weekday pf the date YYYY-01-04
Dim intYYYY0104DaySince As Integer 'YYYY-Www-D days since YYYY-01-04
Dim strYYYYMMDD As String 'The convertet date from YYYY-Www-D to
'YYYY-MM-DD
Dim intWeeksInYearMax As Integer 'Max Weeks in year
' Set some variables
intYYYY = CInt(Left(strWeek, 4)) 'Year part
'Week part
intWww = CInt(Mid(strWeek, InStr(1, strWeek, "W", vbTextCompare) + 1, 2))
intDD = CInt(Right(strWeek, 1)) 'Weekday part
'Checks if the year is a Leap Year
If bIsLeapYear(intYYYY) = True Then
intYYYYILY = 1
Else
intYYYYILY = 0
End If
'If Weekday eq. Thuesday or Year is leapyear and first day is wednesday/Thu
'then there is 53 weeks
If (Weekday(DateSerial(intYYYY, 1, 1), vbMonday) = 4) Or (intYYYYILY = 1 _
And (Weekday(DateSerial(intYYYY, 1, 1), vbMonday) = 3)) Then
intWeeksInYearMax = 53
Else
intWeeksInYearMax = 52
End If
'Not a correct weekdate
If intDD > 7 Or intDD < 1 Or intYYYY < 1900 Or intWww < 1 _
Or intWww > intWeeksInYearMax Then
'WeekToDate = DateSerial(1901, 1, 1) 'Lager feilmelding
MsgBox ("Wrong input date: " & strWeek _
& " - Must be on the format YYYY-Www-D / YYYYWwwD and " _
& Chr(13) & Chr(13) & "Year greater than 1900, " _
& "Week(ww) between 1 and " & intWeeksInYearMax & " for year " _
& intYYYY & " and Day(D) between 1 and 7 (mon=1,sun=7)")
Exit Function
End If
'Sets the date YYYY-01-04: January 4, YYYY.
datYYYY0104 = DateSerial(intYYYY, 1, 4)
' Calculates the Weekday(1=mon, 7=sun) for the date YYYY-01-04
intYYYY0104Weekday = Weekday(datYYYY0104, vbMonday)
'C11= Calculates the number of days from the date YYYY-01-04
intYYYY0104DaySince = (intWww - 1) * 7 + intDD - intYYYY0104Weekday
Select Case intYYYY0104DaySince
'Max tree days difference
Case -7 To -4 ' Dec last year
strYYYYMMDD = CStr(intYYYY - 1) & "-12-" _
& Right("0" & CStr(31 + 4 + intYYYY0104DaySince), 2)
Case -3 To 27 ' January
strYYYYMMDD = CStr(intYYYY) & "-01-" _
& Right("0" & CStr(intYYYY0104DaySince + 4), 2)
Case 28 To (56 + intYYYYILY) ' February
strYYYYMMDD = CStr(intYYYY) & "-02-" _
& Right("0" & CStr(intYYYY0104DaySince - 27), 2)
Case (56 + intYYYYILY + 1) To (87 + intYYYYILY) ' March
strYYYYMMDD = CStr(intYYYY) & "-03-" _
& Right("0" & CStr(intYYYY0104DaySince - 55 - intYYYYILY), 2)
Case (87 + intYYYYILY + 1) To (117 + intYYYYILY) ' April
strYYYYMMDD = CStr(intYYYY) & "-04-" _
& Right("0" & CStr(intYYYY0104DaySince - 86 - intYYYYILY), 2)
Case (117 + intYYYYILY + 1) To (148 + intYYYYILY) ' May
strYYYYMMDD = CStr(intYYYY) & "-05-" _
& Right("0" & CStr(intYYYY0104DaySince - 116 - intYYYYILY), 2)
Case (148 + intYYYYILY + 1) To (178 + intYYYYILY) ' June
strYYYYMMDD = CStr(intYYYY) & "-06-" _
& Right("0" & CStr(intYYYY0104DaySince - 147 - intYYYYILY), 2)
Case (178 + intYYYYILY + 1) To (209 + intYYYYILY) ' July
strYYYYMMDD = CStr(intYYYY) & "-07-" _
& Right("0" & CStr(intYYYY0104DaySince - 177 - intYYYYILY), 2)
Case (209 + intYYYYILY + 1) To (240 + intYYYYILY) ' Aug
strYYYYMMDD = CStr(intYYYY) & "-08-" _
& Right("0" & CStr(intYYYY0104DaySince - 208 - intYYYYILY), 2)
Case (240 + intYYYYILY + 1) To (270 + intYYYYILY) ' Sept
strYYYYMMDD = CStr(intYYYY) & "-09-" _
& Right("0" & CStr(intYYYY0104DaySince - 239 - intYYYYILY), 2)
Case (270 + intYYYYILY + 1) To (301 + intYYYYILY) ' Oct
strYYYYMMDD = CStr(intYYYY) & "-10-" _
& Right("0" & CStr(intYYYY0104DaySince - 269 - intYYYYILY), 2)
Case (301 + intYYYYILY + 1) To (331 + intYYYYILY) ' Nov
strYYYYMMDD = CStr(intYYYY) & "-11-" & Right("0" _
& CStr(intYYYY0104DaySince - 300 - intYYYYILY), 2)
Case (331 + intYYYYILY + 1) To (361 + intYYYYILY) ' Dec
strYYYYMMDD = CStr(intYYYY) & "-12-" _
& Right("0" & CStr(intYYYY0104DaySince - 330 - intYYYYILY), 2)
'Max tree days difference
Case (361 + intYYYYILY + 1) To (365 + intYYYYILY) ' Jan next year
strYYYYMMDD = CStr(intYYYY + 1) & "-01-" _
& Right("0" & CStr(intYYYY0104DaySince - 365 - intYYYYILY + 4), 2)
Case Else
MsgBox ("Wrong input date: " & strWeek _
& " - Must be on the format YYYY-Www-D / YYYYWwwD and " _
& Chr(13) & Chr(13) & "Year greater than 1900, " _
& "Week(ww) between 1 and " & intWeeksInYearMax & " for year " _
& intYYYY & " and Day(D) between 1 and 7 (mon=1,sun=7)")
Exit Function
End Select
'Set the return value
WeekToDate = DateSerial(CInt(Left(strYYYYMMDD, 4)) _
, CInt(Mid(strYYYYMMDD, 6, 2)), CInt(Right(strYYYYMMDD, 2)))
Exit Function
ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$)
End Function
'
|
'
Public Function bIsLeapYear(ByVal intYear As Integer) As Boolean
'******************************************************************************
' Macro created 2003-03-18 by Nikolai Sandved (nsaa@pvv.org
' See Leap Year rule at
' http://www.computerbooksonline.com/program/1032chap.htm#E29E11
' http://en.wikipedia.org/wiki/Leap_year
' http://aa.usno.navy.mil/faq/docs/leap_years.html
' http://www.timeanddate.com/date/leapyear.html
'******************************************************************************
bIsLeapYear = ((intYear Mod 4 = 0) _
And (intYear Mod 100 <> 0) _
Or (intYear Mod 400 = 0))
End Function
'
|
For a very good treatment of the relationship between the gregorian calendar and the international week numbering, see this page http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm.
Microsoft has also make some comments on Error in VB functions BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year.
Some Excel files
The files below is tested on Micorsoft Excel version 2000 and 2002, and on the operating system Windows 2000 and Windows XP Home Edition (SP2) .
PIN-kodehusker
This file creates a 5*8 area with random generated numbers after you have picked 4 numbers (use with credit cards). This is implemented using Diner Clubs idea on how to conceal a PIN number. Here's the file (text only in norwegian, but it should be easy to use regardless. Unzip the xls file after it's downloaded) Excel_PIN-kodehusker.zip ( 20K bytes; last changed: 2006-01-14T20:12:38 CET )
Generic Excel Calendar
The Calendar is generic and should show all year correctly (except 1900). It should be perfect to use as a
template for all Excel-sheets that need to display a full year generic. The Excel Calendar shows very easy all years ahead: 2005, 2006, 2007, 2008,
2009 etc. Yust type in the year in the Calendar sheet.
Download from Excel_Calendar.xls
(272K bytes; last changed: 2006-01-22T00:37:38 CET
)
Some good online calendars can be found here Steffen Thorsen Calendar and time and date.com Calendar. Both with correct implemented week numbers!
Convert a date to week and back to a date
This file contains an Excel function and a Visual Basic for Application (VBA) code for
implementing a ISO 8601 date-week-date conversion (The default Excel function do this incorrectly - See my ISO 8601 page
for more info on this subject).
Download from ISO_8601_Date-Week-Date.xls
( 85K bytes; last changed: 2006-01-13T20:48:25 CET
)
Get environment info about your PC
This file contains an Excel function that write out information about International settings, Microsoft Office Program
Installed and Environment settings. (Typically you need this information when you write a VBA code and need to find out some
system information about the users PC.)
Download from (unzip the xls file after it's downloaded) ExcelInternational.zip
( 22K bytes; last changed: 2006-01-14T20:12:29 CET
)
Create a dependent input list
This file contains an example on how to create a list element dependent on a prior entered value by the user. If the user
enter Britain, then the list contains British cities, Enter the user Denmark, the list contains Danish cities. See Picture below.
Download from Excel_ListDependent.xls
( 75K bytes; last changed:
2006-01-22T00:37:52 CET )
Comparison of Functions Calculating the Easter Date
This file Contains a implemention of and a comparison of some algorithm appearing on the web. There's a big problem with many of the proposals on the web. They only calculate correct Easter date for a small number of years. For example the top ranking search on Google with the word
For USA style dates (mm/dd/yyyy), use
=FLOOR("5/"&DAY(MINUTE(B2/38)/2+56)&"/"&B2,7)-34
For European style dates (dd/mm/yyyy), use
=FLOOR(DAY(MINUTE(B2/38)/2+56)&"/5/"&B2,7)-34
This function return correct Easter dates in the year span 1900/2078
There is NO REASON for using funtion that is dependent on what you use as a system date: Use these functions instead (Correct for 1900/2078)
=FLOOR(DATE(B2;5;DAY(MINUTE(B2/38)/2+56));7)-34
In the sheet below I've implementet the algorithm proposed in the Calendar FAQ. This function
should return correct Easter dates for all years in the Gregorian Calendar and even for the Julian Calendar. I've named the
function EasterDateCalendarFAQ(). This function is compared to a lot of others.
Download from Excel_EasterDate.zip
(203K bytes; last changed: 2006-01-14T20:12:37 CET )
Here's the function written out:
Public Function EasterDateCalendarFAQ(ByVal strYear As String) As Date
'******************************************************************************
' Macro created 2003-03-24 by Nikolai Sandved (nsaa@pvv.org)
' Returns the Easter Day for a given year
'
' Input:
' strYear - Year (It should be correct Beetween 532 and year 4000 approx)
'
' Output:
' EasterDateCalendarFAQ - Returns the Easter Day for the given year strYear
'
'Based on the Calendar FAQ formula
'http://www.tondering.dk/claus/cal/node3.html#SECTION003120000000000000000
'
'The formulas presented here gives the same ansvers:
'
'http://aa.usno.navy.mil/faq/docs/easter.html
'NB: "...Easter can never occur before March 22 or later than April 25..."
'
'A good reference:
' http://www.ortelius.de/kalender/east_en.html
'Easter date FAQ:
' http://www.cs.rutgers.edu/pub/soc.religion.christian/faq/easter-date
'
'If you don't want to use VB Code in Excel, use this formula:
'The Excel formula below gives the correct ansver between 1900 and 2078
'On 2079 it fails with 7 days to early
'Next miss is on 2204 when it fails with 7 days to early
'Year in cell A1
'=FLOOR(DATE(A1;5;DAY(MINUTE(A1/38)/2+56));7)-34
'or
'=FLOOR(DATE(A1,5,DAY(MINUTE(A1/38)/2+56)),7)-34
'depending on your system using , or ;
'
'******************************************************************************
'Define local variables
Dim intYear As Integer 'Year
Dim intGolden As Integer 'Golden Number - 1
Dim intEpact23 As Integer '23-Epact (modulo 30)
Dim intPFmoon As Integer 'Number of days from march 21 to the
'Paschal full moon
Dim intPFmoonWeekday As Integer '0=sunday, 1=monday
Dim intPFmoonSunday As Integer 'Sunday before or on Paschal Fullmoon
'(Between -6 and 28)
Dim intEasterMonth As Integer 'Easter Month
Dim intEasterDay As Integer 'Easter Day
Dim intCentury As Integer 'Century of intYear
'Convert inputyear to integer
intYear = CInt(strYear)
'The Golden number minus 1
intGolden = (intYear Mod 19)
'Gregorian dates of Easter are computed for the years from 1583 on
'(first Easter sunday after the introduction of the calendar),
'Julian dates of Easter from 532 CE on (beginning of Dionysius Exiguus's
'Easter tables). There are countries in which Easter was celebrated
'on different days. These differences are not considered here
'http://www.ortelius.de/kalender/forme_en.html
'Norway/Denmark changed to Gregorian Calendar on 1700
'for other countries than Denmark/Norway, check this page
'http://www.tondering.dk/claus/cal/node3.html#SECTION00324000000000000000
'Denmark (including Norway): 18 Feb 1700 was followed by 1 Mar 1700
If (intYear <= 1583) Then
'/* JULIAN CALENDAR */
'The Julian calendar, introduced by Juliius Caesar in -45,
'was a solar calendar with months of fixed lengths. Every fourth
'year an intercalary day was added to maintain synchrony between
'the calendar year and the tropical year. It served as a standard
'for European civilization until the Gregorian Reform of +1582.
intPFmoon = (19 * intGolden + 15) Mod 30
intPFmoonWeekday = (intYear + (intYear \ 4) + intPFmoon) Mod 7
Else
'/* GREGORIAN CALENDAR */
intCentury = intYear \ 100
intEpact23 = (intCentury - intCentury \ 4 - (8 * intCentury + 13) \ 25 _
+ 19 * intGolden + 15) Mod 30
intPFmoon = intEpact23 - (intEpact23 \ 28) _
* (1 - (29 \ (intEpact23 + 1)) * ((21 - intGolden) \ 11))
intPFmoonWeekday = (intYear + (intYear \ 4) + intPFmoon + 2 _
- intCentury + (intCentury \ 4)) Mod 7
End If
intPFmoonSunday = intPFmoon - intPFmoonWeekday
intEasterMonth = 3 + ((intPFmoonSunday + 40) \ 40)
intEasterDay = intPFmoonSunday + 28 - 31 * (intEasterMonth \ 4)
EasterDateCalendarFAQ = DateSerial(intYear, intEasterMonth, intEasterDay)
End Function
|
All the Easters from year 1900 to year 4000 is listed in this text file:
Download from easterdates.shtml
( 24K bytes; last changed: 2006-01-19T21:21:32 CET)
Modulus 10 "2-1-2" functions
The Modulus 10 "2-1-2" Contol digit is used in many applications. For example the Norwegian company BBS uses it. See chapter 5.1 in Systemspesifikasjon for avtalegiro (Norwegian)
Public Function ControlDigit(ByVal strNumber As String) As Byte
'******************************************************************************
'
' Function ControlDigit
' Function created 2004-08-17 by Nikolai Sandved (nsaa@pvv.org)
'
' Description: This function returns the Control Digit according to Modulus 10
' "2-1-2" with weights 2 1 2 1 2 1 2 ...
'
' A Norwegian explanation
' http://www.bbs.no/avtalegiro/bedrift/Brukerhandb/avtg_systemhandbok030601.pdf
Dim bytSiffer As Byte
Dim strNumberLeft As String
Dim bytMult As Byte
Dim bytProd As Byte
Dim strProd As String
Dim intSifferSum As Integer
Dim intTeller As Integer
Dim strNumberOld As String
bytSiffer = CByte(Right(strNumber, 1))
strNumberLeft = Left$(strNumber, Len(strNumber) - 1)
strNumberOld = strNumberLeft
bytMult = 2
intSifferSum = 0
intTeller = 1
Do While (strNumberOld <> "")
bytProd = bytSiffer * bytMult
bytMult = bytMult + ((-1) ^ intTeller) 'Changes from 2 1 2 1 2 1 2 etc.
intTeller = intTeller + 1
If bytProd > 9 Then
strProd = CStr(bytProd)
intSifferSum = intSifferSum + CByte(Left(strProd, 1)) + CByte(Right(strProd, 1))
Else
intSifferSum = intSifferSum + bytProd
End If
strNumberOld = strNumberLeft
If (strNumberLeft <> "") Then
bytSiffer = CByte(Right(strNumberLeft, 1))
strNumberLeft = Left$(strNumberOld, Len(strNumberOld) - 1)
End If
Loop
'Returns 0 if 10 - siffersum = 10, else return 10 - siffersum
ControlDigit = CByte(Right(CStr(10 - CByte(Right(intSifferSum, 1))), 1))
'
End Function
|
Public Function ControlNumber(ByVal strNumber As String) As Boolean
'******************************************************************************
'
' Function ControlNumber
' Function created 2004-08-17 by Nikolai Sandved (nsaa@pvv.org)
'
' Description: This function controls if the Number is coded according to
' Modulus 10 "2-1-2" with weights 2 1 2 1 2 1 2 ...
'
' A Norwegian explanation
' http://www.bbs.no/avtalegiro/bedrift/Brukerhandb/avtg_systemhandbok030601.pdf
Dim bytControlDigit As String
'Calculates the ControlDigit
bytControlDigit = ControlDigit(Left(strNumber, Len(strNumber) - 1))
'If it is the same - Ok
If bytControlDigit = Right(strNumber, 1) Then
ControlNumber = True
Else
ControlNumber = False
End If
'Stop
'
End Function
|
What Color is used in font, interior and borders
I've made two function Which count or return the color/colour used in Excel Cells
I've made an example sheet Excel_Color.7z. Use the 7-Zip program (freeware under GNU LGPL) to extract the Excel file Excel_Color.xls from the 7z archive.
The first function counts the number of times a given Color (as given in Excels ColorIndex) appear in an area
Public Function colorCount(ByVal rngArea As Range, ByVal lngCol As Long _
, Optional ByVal strType As String) As Long
'******************************************************************************
'
' Public Function colorCount
'
' Description: The macro returns the number of a given background color
' in a given range
'
' Input
' rngArea - A range (ex. Range("A1:B2"))
'
' lngCol - A integer between 0(Black) and 56
'
' strType - Optional. Can count "Borders" or "Font" instead of the default
' Interior color.
'
'
'
' Output
'
' Example
'
' =colorCount(B3:E8,0) returns the number of cells in the area B3:E8 with
' black(0) Interior color
'
' =colorCount(B3:E8,0,"Font") returns the number of cells in the area B3:E8 with
' black(0) Fonts
'
' Date Name Description
'------------------------------------------------------------------------------
' 2004-02-26 Nikolai Sandved Created
' 2005-04-13 Nikolai Sandved Publ. on http://www.pvv.org/~nsaa/excel.shtml
'
'******************************************************************************
'
'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsActive As Worksheet 'The active worksheet
Dim c As Range
Dim cntCol As Long
Dim firstAddress
'** Declare variables
Set wbm = ActiveWorkbook
Set wsActive = wbm.ActiveSheet
cntCol = 0
Select Case strType
Case "Borders"
For Each c In wsActive.Range(rngArea.Address)
If c.Borders.ColorIndex = lngCol Then
cntCol = cntCol + 1
End If
Next c
Case "Font"
For Each c In wsActive.Range(rngArea.Address)
If c.Font.ColorIndex = lngCol Then
cntCol = cntCol + 1
End If
Next c
Case Else
For Each c In wsActive.Range(rngArea.Address)
If c.Interior.ColorIndex = lngCol Then
cntCol = cntCol + 1
End If
Next c
End Select
'Returns value
colorCount = cntCol
'Cleans
cntCol = 0
Set wsActive = Nothing
Set wbm = Nothing
End Function
|
The second function returns the Color (the ColorIndex number) used in the given cell
Public Function colorInCell(ByVal rngArea As Range, _
Optional ByVal strType As String) As Long
'******************************************************************************
'
' Public Function colorInCell
'
' Description: The macro return the colour in a given cell
'
' Input
' rngArea - A cell (ex. Range("A1"))
'
' strType - Optional. Can count "Borders" or "Font" instead of the default
' Interior color.
' Output
'
' Example
'
' =colorInCell(B3) returns the Interior color for B3
'
' =colorInCell(B3,"Font") returns Font Color in Cell B3
'
' =colorInCell(B3,"Borders") returns Border Color in Cell B3
'
' Date Name Description
'------------------------------------------------------------------------------
' 2005-04-13 Nikolai Sandved Publ. on http://www.pvv.org/~nsaa/excel.shtml
'
'******************************************************************************
'
'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsActive As Worksheet 'The active worksheet
Dim cntCol As Long
'** Declare variables
Set wbm = ActiveWorkbook
Set wsActive = wbm.ActiveSheet
cntCol = 0
Select Case strType
Case "Borders"
cntCol = wsActive.Range(rngArea.Address).Borders.ColorIndex
Case "Font"
cntCol = wsActive.Range(rngArea.Address).Font.ColorIndex
Case Else
cntCol = wsActive.Range(rngArea.Address).Interior.ColorIndex
End Select
'Returns value
colorInCell = cntCol
'Cleans
cntCol = 0
Set wsActive = Nothing
Set wbm = Nothing
End Function
|
Translations in international Editions of Excel (Norwegian/Swedish/English)
In the international Editions of Excel it's possible to get 'everything' translated to a lot of different Languages (have Microsoft ever heard about Babel?). The problem with this is when it's not done completely and when the documentation is missing. A lot of Norwegians, Swedes and so fort, learn the native language function names and become very bad at finding solutions to problem at the Internet. Ex. Try to find information about the function
BUG - In the Norwegian translation they've translated ISERR() to ERF(). This function already exists in the english version ERF() and the Engineering functions is not translated (Why?). I.e. It's not possible to use this function in the Norwegian translation (only tested in 2000 and 2002). It's really hopeless!
The table with all the translations below was made by using the superb Excel add-on ASAP Utilities.
Wishes for next release of Excel/Word
In Microsoft Excel 2003 there are a lot of missing features. This is a short list of wishes:
Excel
- There should be possible to add pathinfo in the Header that automatically updates (now only the filename is accessible &F).
- Point above - Current filename and pathinfo should be accessible through the Excel functions.
- The date should not show 1900 as a Leap Year.
- The date should span dates befor 1900(i.e. accept negative internal date values).
- The Week function should return weeks according to ISO 8601. Even better it should be possible to format the date field with ISO year, and weeks as in Oracle (iyyy for ISO year and ww for ISO week). See my ISO 8601 page for more info on this subject.
- Statistical Functions: =PEARSON is a bad choice for all Excel versions earlier than 2003. Use =CORREL. In Excel 2003 these two function produce the same results ... i.e. never use =PEARSON. Why not state this Microsoft (only support this function unseen for all new releases)?
- Statistical functions: According to Microsoft many of their statistical functions produce wrong results in earlier version of Excel. According to Microsoft this is corrected in Excel 2003.
- Increase the number of rows supported. Today only 65536 rows times 256 columns is possible. Why not let the PC's memory be the limit? In the OpenOffice area have published a detailed analysis of the row limit problem for the Calc-application Increasing the row limit above 32000 rows (it's in the same way relevant to Excel).
- International Edition of Excel: In the Norwegian version, Microsoft has translated all the Excel functions to Norwegian. This is BAD BAD BAD. (Nearly) all (online) help, books etc. is published with (english) standard funtion names. Please make the standard names available in all editions! (Examples in the Norwegian version: =NÅ (=NOW), =SUMMER (=SUM), =GJENNOMSNITT (=AVERAGE), =FINN.RAD (=VLOOKUP)). Here's an overview of Dansih, Geman and Norwegian Excel function names compared to the standard english names http://www.sslug.dk/locale/kenneth/dom/apps/gnumeric-hacks/intlfuncn.html. In another section in this document I've done a complete translation of all translated functions in Norwegian and Swedish. See Norwegian(norsk) and Swedish(svenska) translations of Excel functions. In Visual Basic for Application (VBA) you still need to use the standard names... This is so bad that I nearly don't have words for it. What the ¤%&/#¤ do they think at Microsoft doing this? You don't translate (partly!) like this! I say: Don't translate functions at all ... (or at least make the english function names available togheter with the translated ones).
- BUG: ISERR() is translated to ERF(), but not the Engineering functions which also have an ERF-function, now impossible to use in the Norwegian translation...
- It should not be possible to break a password protected area in Excel. Possible in the sense that the password key is at least 128 bit (?).
- Add an Easter Date function. For example like this one Calculate Eastern.
- Excel 2000 do not save html files correctly. It uses elements wrongly according to the W3 Markup Validator
Word
- Cross References should be updated when the document is saved, not only when you print it or manually update the cross reference.
- There should be possible to Left and Right adjust in the same line (Today you need to use a table with two colums to accomplish this).
[ProbabilityOf.com] | [Lotto probabilities] | [Poker probabilities] | [DOOM] | [Easter Dates] | [Einstein Quiz] | [Escape Game] | [Excel] | [ISO 8601] | [ISO Clock] | [IQ Tests] | [Symmetrical Democracy] | [The Toilet Paper Roll Poll] | [Women=Problems] | [WRC]
|
|
|
| If you are interested in playing poker on the internet, I recomend Everest Poker. By signing up with Everest Poker you get an USD 100 welcome bonus. I highly recommends this site where there is an excellent selection of tables and an unusually high proportion of fish (weak players)! | |
Current local time ()
This page () was downloaded at and was last updated at
Did you see any errors on, or have other comments to this page? Please send Nikolai Sandved an e-mail at webmaster@probabilityof.com
"The views expressed herein are the personal views and opinions of the current user and are not made on behalf of his or her current employer."