	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
- Excel
- Word

Please feel free to contact me on email at Nikolai Sandved ([email protected])

Information about Excel

In this section I will add information about Excel that is not so commonly known, but may affect many users

Back to the top

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

Back to the top

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:
Code

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

Code
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.

Back to the top

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

Code
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 ([email protected]) ' ' 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

Code


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 ([email protected])
'
' 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

Code
Public Function WeekToDate(ByVal strWeek As String) As Date '****************************************************************************** ' ' Macro created 2001-01-18 by Nikolai Sandved ([email protected] ' ' 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 '

Code


Public Function WeekToDate(ByVal strWeek As String) As Date
'******************************************************************************
'
' Macro created 2001-01-18 by Nikolai Sandved ([email protected]
'
' 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
'

Code
' Public Function bIsLeapYear(ByVal intYear As Integer) As Boolean '**************************************************************************** ' Macro created 2003-03-18 by Nikolai Sandved ([email protected] ' 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 '

Code

'
Public Function bIsLeapYear(ByVal intYear As Integer) As Boolean
'******************************************************************************
' Macro created 2003-03-18 by Nikolai Sandved ([email protected]
' 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.

Back to the top

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 )

Back to the top

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!

Back to the top

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 )

Back to the top

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 )

Back to the top

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 )

Back to the top

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

direct us to the site http://www.cpearson.com/excel/holidays.htm. Their proposal for a VBA function: Public Function EasterDate(Yr As Integer) As Date returns ONLY correct Easter dates for the year span 1900 to 2099. They also have a proposal for a way to solve it without writing your own functions:

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:
Code

Public Function EasterDateCalendarFAQ(ByVal strYear As String) As Date '****************************************************************************** ' Macro created 2003-03-24 by Nikolai Sandved ([email protected]) ' 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

Code
Public Function EasterDateCalendarFAQ(ByVal strYear As String) As Date '**************************************************************************** ' Macro created 2003-03-24 by Nikolai Sandved ([email protected]) ' 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)

Back to the top

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)
Code

Public Function ControlDigit(ByVal strNumber As String) As Byte '****************************************************************************** ' ' Function ControlDigit ' Function created 2004-08-17 by Nikolai Sandved ([email protected]) ' ' 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

Code

Public Function ControlNumber(ByVal strNumber As String) As Boolean '****************************************************************************** ' ' Function ControlNumber ' Function created 2004-08-17 by Nikolai Sandved ([email protected]) ' ' 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

Code
Public Function ControlDigit(ByVal strNumber As String) As Byte '****************************************************************************** ' ' Function ControlDigit ' Function created 2004-08-17 by Nikolai Sandved ([email protected]) ' ' 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

Code
Public Function ControlNumber(ByVal strNumber As String) As Boolean '****************************************************************************** ' ' Function ControlNumber ' Function created 2004-08-17 by Nikolai Sandved ([email protected]) ' ' 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

Back to the top

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
Code

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

Code
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
Code

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

Code
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

Back to the top

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

at Microsoft.no. You don't (per 2005-04-10). You need to know that this functions name is VLOOKUP and the search the international site to find information(all english Excel 2003 function documented here: http://office.microsoft.com/en-us/assistance/HP052042111033.aspx).

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.

Swedish (Svensk) Norwegian (Norsk) Standard English (Engelsk) Category Office 2003

=ABS() =ABS() =ABS() Math and Trigonometry Functions

=ADRESS() =ADRESSE() =ADDRESS() Lookup and Reference

=ANROPA() =ANROP() =CALL()

=ANTAL() =ANTALL() =COUNT() Statistical Functions

=ANTAL.OM() =ANTALL.HVIS() =COUNTIF() Statistical Functions

=ANTALV() =ANTALLA() =COUNTA() Statistical Functions

=BETALNING() =AVDRAG() =PMT() Financial Functions

=AVKORTA() =AVKORT() =TRUNC() Math and Trigonometry Functions

=AVRUNDA() =AVRUND() =ROUND() Math and Trigonometry Functions

=RUNDA.UPP() =AVRUND.GJELDENDE.MULTIPLUM() =CEILING() Math and Trigonometry Functions

=RUNDA.NER() =AVRUND.GJELDENDE.MULTIPLUM.NED() =FLOOR() Math and Trigonometry Functions

=AVRUNDA.NED�T() =AVRUND.NED() =ROUNDDOWN() Math and Trigonometry Functions

=AVRUNDA.UPP�T() =AVRUND.OPP() =ROUNDUP() Math and Trigonometry Functions

=UDDA() =AVRUND.TIL.ODDETALL() =ODD() Math and Trigonometry Functions

=J�MN() =AVRUND.TIL.PARTALL() =EVEN() Math and Trigonometry Functions

=KVADAVV() =AVVIK.KVADRERT() =DEVSQ() Statistical Functions

=BESSELI() =BESSELI() =BESSELI() Engineering Functions

=BETAF�RD() =BETA.FORDELING() =BETADIST() Statistical Functions

=BINOMF�RD() =BINOM.FORDELING() =BINOMDIST() Statistical Functions

=BYT.UT() =BYTT.UT() =SUBSTITUTE() Text and Data Functions

=CELL() =CELLE() =CELL() Information Functions

=DAG() =DAG() =DAY() Date and Time Functions

=DAGAR360() =DAGER360() =DAYS360() Date and Time Functions

=DANTAL() =DANTALL() =DCOUNT()

=DANTALV() =DANTALLA() =DCOUNTA()

=DATUM() =DATO() =DATE() Date and Time Functions

=DATUMV�RDE() =DATOVERDI() =DATEVALUE() Date and Time Functions

=DB() =DAVSKR() =DB()

=DEGAVSKR() =DEGRAVS() =DDB()

=DELSUMMA() =DELSUM() =SUBTOTAL()

=DELTA() =DELTA() =DELTA() Engineering Functions

=EXTEXT() =DELTEKST() =MID()

=DMEDEL() =DGJENNOMSNITT() =DAVERAGE()

=DH�MTA() =DHENT() =DGET()

=DISC() =DISC() =DISC()

=DMAX() =DMAKS() =DMAX()

=DMIN() =DMIN() =DMIN()

=DOLLARDE() =DOLLARDE() =DOLLARDE()

=DPRODUKT() =DPRODUKT() =DPRODUCT()

=DSTDAV() =DSTDAV() =DSTDEV()

=DSTDAVP() =DSTDAVP() =DSTDEVP()

=DSUMMA() =DSUMMER() =DSUM()

=DURATION() =DURATION() =DURATION()

=DVARIANS() =DVARIANS() =DVAR()

=DVARIANSP() =DVARIANSP() =DVARP()

=EDATE() =EDATE() =EDATE()

=EFFECT() =EFFECT() =EFFECT()

=EXAKT() =EKSAKT() =EXACT()

=EXP() =EKSP() =EXP()

=EXPONF�RD() =EKSP.FORDELING() =EXPONDIST()

=ELLER() =ELLER() =OR()

=EOMONTH() =EOMONTH() =EOMONTH()

=RAL�N() =ER.AVDRAG() =ISPMT()

=�RF() =ERF() =ISERR()

=ERF() error message, incredible! =ERF()

=ERFC() =ERFC() =ERFC()

=�RFEL() =ERFEIL() =ISERROR()

=�REJTEXT() =ERIKKETEKST() =ISNONTEXT()

=�RSAKNAD() =ERIT() =ISNA()

=�RLOGISK() =ERLOGISK() =ISLOGICAL()

=�RREF() =ERREF() =ISREF()

=ERS�TT() =ERSTATT() =REPLACE()

=�RTAL() =ERTALL() =ISNUMBER()

=�RTEXT() =ERTEKST() =ISTEXT()

=�RTOM() =ERTOM() =ISBLANK()

=FACTDOUBLE() =FACTDOUBLE() =FACTDOUBLE()

=FAKULTET() =FAKULTET() =FACT()

=FASTTAL() =FASTSATT() =FIXED()

=FEL.TYP() =FEIL.TYPE() =ERROR.TYPE()

=FF�RD() =FFORDELING() =FDIST()

=FINV() =FFORDELING.INVERS() =FINV()

=HITTA() =FINN() =FIND()

=LETAKOLUMN() =FINN.KOLONNE() =HLOOKUP()

=LETARAD() =FINN.RAD() =VLOOKUP()

=FISHER() =FISHER() =FISHER()

=FISHERINV() =FISHERINV() =FISHERINV()

=F�RSKJUTNING() =FORSKYVNING() =OFFSET()

=TECKEN() =FORTEGN() =SIGN()

=FREKVENS() =FREKVENS() =FREQUENCY()

=FTEST() =FTEST() =FTEST()

=FVSCHEDULE() =FVSCHEDULE() =FVSCHEDULE()

=GAMMAF�RD() =GAMMAFORDELING() =GAMMADIST()

=GAMMAINV() =GAMMAINV() =GAMMAINV()

=GAMMALN() =GAMMALN() =GAMMALN()

=GCD() =GCD() =GCD()

=GESTEP() =GESTEP() =GESTEP()

=MEDEL() =GJENNOMSNITT() =AVERAGE()

=GEOMEDEL() =GJENNOMSNITT.GEOMETRISK() =GEOMEAN()

=HARMMEDEL() =GJENNOMSNITT.HARMONISK() =HARMEAN()

=AVERAGEA() =GJENNOMSNITTA() =AVERAGEA()

=MEDELAVV() =GJENNOMSNITTSAVVIK() =AVEDEV()

=REP() =GJENTA() =REPT()

=GRADER() =GRADER() =DEGREES()

=KRITBINOM() =GRENSE.BINOM() =CRITBINOM()

=HELTAL() =HELTALL() =INT()

=H�MTA.PIVOTDATA() =HENTPIVOTDATA() =GETPIVOTDATA()

=HEX2BIN() =HEX2BIN() =HEX2BIN()

=HEX2DEC() =HEX2DEC() =HEX2DEC()

=HEX2OCT() =HEX2OCT() =HEX2OCT()

=OM() =HVIS() =IF()

=HYPERL�NK() =HYPERKOBLING() =HYPERLINK()

=HYPGEOMF�RD() =HYPGEOM.FORDELING() =HYPGEOMDIST()

=H�GER() =H�YRE() =RIGHT()

=IDAG() =IDAG() =TODAY() Date and Time Functions

=ICKE() =IKKE() =NOT()

=IMABS() =IMABS() =IMABS()

=IMAGINARY() =IMAGINARY() =IMAGINARY()

=IMARGUMENT() =IMARGUMENT() =IMARGUMENT()

=IMCONJUGATE() =IMCONJUGATE() =IMCONJUGATE()

=IMCOS() =IMCOS() =IMCOS()

=IMDIV() =IMDIV() =IMDIV()

=IMEXP() =IMEXP() =IMEXP()

=IMLN() =IMLN() =IMLN()

=IMLOG10() =IMLOG10() =IMLOG10()

=IMLOG2() =IMLOG2() =IMLOG2()

=IMPOWER() =IMPOWER() =IMPOWER()

=IMPRODUCT() =IMPRODUCT() =IMPRODUCT()

=IMREAL() =IMREAL() =IMREAL()

=IMSIN() =IMSIN() =IMSIN()

=IMSQRT() =IMSQRT() =IMSQRT()

=IMSUB() =IMSUB() =IMSUB()

=IMSUM() =IMSUM() =IMSUM()

=INDEX() =INDEKS() =INDEX()

=INDIREKT() =INDIREKTE() =INDIRECT()

=INFO() =INFO() =INFO()

=INTRATE() =INTRATE() =INTRATE()

=BETAINV() =INVERS.BETA.FORDELING() =BETAINV()

=CHI2INV() =INVERS.KJI.FORDELING() =CHIINV()

=IR() =IR() =IRR()

=ISEVEN() =ISEVEN() =ISEVEN()

=ISODD() =ISODD() =ISODD()

=SAKNAS() =IT() =NA()

=SAMMANFOGA() =KJEDE.SAMMEN() =CONCATENATE()

=CHI2F�RD() =KJI.FORDELING() =CHIDIST()

=CHI2TEST() =KJI.TEST() =CHITEST()

=KOD() =KODE() =CODE()

=KOLUMN() =KOLONNE() =COLUMN()

=KOLUMNER() =KOLONNER() =COLUMNS()

=KOMBIN() =KOMBINASJON() =COMBIN()

=KONFIDENS() =KONFIDENS() =CONFIDENCE()

=KORREL() =KORRELASJON() =CORREL()

=KOVAR() =KOVARIANS() =COVAR()

=TOPPIGHET() =KURT() =KURT()

=EXPREGR() =KURVE() =LOGEST()

=KVARTIL() =KVARTIL() =QUARTILE()

=LCM() =LCM() =LCM()

=L�NGD() =LENGDE() =LEN()

=LINAVSKR() =LINAVS() =SLN()

=LN() =LN() =LN()

=LOG() =LOG() =LOG()

=LOG10() =LOG10() =LOG10()

=LOGINV() =LOGINV() =LOGINV()

=LOGNORMF�RD() =LOGNORMFORD() =LOGNORMDIST()

=MAXA() =MAKSA() =MAXA()

=MDETERM() =MDETERM() =MDETERM()

=MDURATION() =MDURATION() =MDURATION()

=MEDIAN() =MEDIAN() =MEDIAN()

=MIN() =MIN() =MIN()

=MINA() =MINA() =MINA()

=MINUT() =MINUTT() =MINUTE() Date and Time Functions

=MINVERT() =MINVERS() =MINVERSE()

=MMULT() =MMULT() =MMULT()

=MODIR() =MODIR() =MIRR()

=TYPV�RDE() =MODUS() =MODE()

=MROUND() =MROUND() =MROUND()

=MULTINOMIAL() =MULTINOMIAL() =MULTINOMIAL()

=M�NAD() =M�NED() =MONTH() Date and Time Functions

=N() =N() =N()

=MINSTA() =N.MINST() =SMALL()

=ST�RSTA() =N.ST�RST() =LARGE()

=NEGBINOMF�RD() =NEGBINOM.FORDELING() =NEGBINOMDIST()

=NETWORKDAYS() =NETWORKDAYS() =NETWORKDAYS()

=NETNUV�RDE() =NNV() =NPV()

=NOMINAL() =NOMINAL() =NOMINAL()

=NORMF�RD() =NORMALFORDELING() =NORMDIST()

=STANDARDISERA() =NORMALISER() =STANDARDIZE()

=NORMINV() =NORMINV() =NORMINV()

=NU() =N�() =NOW()

=NUV�RDE() =N�VERDI() =PV()

=OCT2BIN() =OCT2BIN() =OCT2BIN()

=OCT2DEC() =OCT2DEC() =OCT2DEC()

=OCT2HEX() =OCT2HEX() =OCT2HEX()

=ODDFPRICE() =ODDFPRICE() =ODDFPRICE()

=ODDFYIELD() =ODDFYIELD() =ODDFYIELD()

=ODDLPRICE() =ODDLPRICE() =ODDLPRICE()

=ODDLYIELD() =ODDLYIELD() =ODDLYIELD()

=OCH() =OG() =AND()

=OMR�DEN() =OMR�DER() =AREAS()

=UPPH�JT.TILL() =OPPH�YD.I() =POWER()

=PEARSON() =PEARSON() =PEARSON()

=PERIODER() =PERIODER() =NPER()

=PERMUT() =PERMUTER() =PERMUT()

=PERCENTIL() =PERSENTIL() =PERCENTILE()

=PI() =PI() =PI()

=POISSON() =POISSON() =POISSON()

=PRICE() =PRICE() =PRICE()

=PRICEDISC() =PRICEDISC() =PRICEDISC()

=PRICEMAT() =PRICEMAT() =PRICEMAT()

=PRODUKT() =PRODUKT() =PRODUCT()

=PREDIKTION() =PROGNOSE() =FORECAST()

=PROCENTRANG() =PROSENTDEL() =PERCENTRANK()

=QUOTIENT() =QUOTIENT() =QUOTIENT()

=RAD() =RAD() =ROW()

=RADER() =RADER() =ROWS()

=RADIANER() =RADIANER() =RADIANS()

=RANDBETWEEN() =RANDBETWEEN() =RANDBETWEEN()

=RANG() =RANG() =RANK()

=RBETALNING() =RAVDRAG() =IPMT()

=RECEIVED() =RECEIVED() =RECEIVED()

=REGISTRERA.ID() =REGISTRER.ID() =REGISTER.ID()

=ST�DA() =RENSK() =CLEAN()

=R�NTA() =RENTE() =RATE()

=REST() =REST() =MOD()

=REGR() =RETTLINJE() =LINEST()

=RKV() =RKVADRAT() =RSQ()

=ROMERSK() =ROMERTALL() =ROMAN()

=ROT() =ROT() =SQRT()

=PASSA() =SAMMENLIGNE() =MATCH()

=SANT() =SANN() =TRUE()

=SANNOLIKHET() =SANNSYNLIG() =PROB()

=SEKUND() =SEKUND() =SECOND()

=SERIESSUM() =SERIESSUM() =SERIESSUM()

=SIN() =SIN() =SIN()

=SINH() =SINH() =SINH()

=SNEDHET() =SKJEVFORDELING() =SKEW()

=SK�RNINGSPUNKT() =SKJ�RINGSPUNKT() =INTERCEPT()

=SLUTV�RDE() =SLUTTVERDI() =FV()

=LETAUPP() =SL�.OPP() =LOOKUP()

=GEMENER() =SM�() =LOWER()

=SQRTPI() =SQRTPI() =SQRTPI()

=STDFELYX() =STANDARDFEIL() =STEYX()

=STDAV() =STDAV() =STDEV()

=STDAVP() =STDAVP() =STDEVP()

=STDEVA() =STDAVVIKA() =STDEVA()

=STDEVPA() =STDAVVIKPA() =STDEVPA()

=LUTNING() =STIGNINGSTALL() =SLOPE()

=INITIAL() =STOR.FORBOKSTAV() =PROPER()

=VERSALER() =STORE() =UPPER()

=MAX() =ST�RST() =MAX()

=SUMMA() =SUMMER() =SUM()

=SUMMA.OM() =SUMMERHVIS() =SUMIF()

=KVADRATSUMMA() =SUMMERKVADRAT() =SUMSQ()

=PRODUKTSUMMA() =SUMMERPRODUKT() =SUMPRODUCT()

=SUMMAX2MY2() =SUMMERX2MY2() =SUMX2MY2()

=SUMMAX2PY2() =SUMMERX2PY2() =SUMX2PY2()

=SUMMAXMY2() =SUMMERXMY2() =SUMXMY2()

=S�K() =S�K() =SEARCH()

=T() =T() =T()

=TAN() =TAN() =TAN()

=TANH() =TANH() =TANH()

=TBILLEQ() =TBILLEQ() =TBILLEQ()

=TBILLPRICE() =TBILLPRICE() =TBILLPRICE()

=TBILLYIELD() =TBILLYIELD() =TBILLYIELD()

=TECKENKOD() =TEGNKODE() =CHAR()

=TEXT() =TEKST() =TEXT()

=ANTAL.TOMMA() =TELLBLANKE() =COUNTBLANK()

=TF�RD() =TFORDELING() =TDIST()

=KLOCKSLAG() =TID() =TIME() Date and Time Functions

=TIDV�RDE() =TIDSVERDI() =TIMEVALUE() Date and Time Functions

=SLUMP() =TILFELDIG() =RAND()

=TIMME() =TIME() =HOUR() Date and Time Functions

=TINV() =TINV() =TINV()

=TRANSPONERA() =TRANSPONER() =TRANSPOSE()

=TREND() =TREND() =TREND()

=RENSA() =TRIMME() =TRIM()

=TRIMMEDEL() =TRIMMET.GJENNOMSNITT() =TRIMMEAN()

=TTEST() =TTEST() =TTEST()

=VECKODAG() =UKEDAG() =WEEKDAY() Date and Time Functions

=FALSKT() =USANN() =FALSE()

=VALUTA() =VALUTA() =DOLLAR()

=VARIANS() =VARIANS() =VAR()

=VARA() =VARIANSA() =VARA()

=VARIANSP() =VARIANSP() =VARP()

=VARPA() =VARIANSPA() =VARPA()

=EXPTREND() =VEKST() =GROWTH()

=V�LJ() =VELG() =CHOOSE()

=V�NSTER() =VENSTRE() =LEFT()

=TEXTNUM() =VERDI() =VALUE()

=VDEGRAVSKR() =VERDIAVS() =VDB()

=V�RDETYP() =VERDITYPE() =TYPE()

=WEEKNUM() =WEEKNUM() =WEEKNUM() Date and Time Functions

=WEIBULL() =WEIBULL.FORDELING() =WEIBULL()

=WORKDAY() =WORKDAY() =WORKDAY() Date and Time Functions

=XIRR() =XIRR() =XIRR()

=XL97() =XL97() =XL97()

=XNPV() =XNPV() =XNPV()

=YEARFRAC() =YEARFRAC() =YEARFRAC()

=YIELD() =YIELD() =YIELD()

=YIELDDISC() =YIELDDISC() =YIELDDISC()

=YIELDMAT() =YIELDMAT() =YIELDMAT()

=ZTEST() =ZTEST() =ZTEST()

=�R() =�R() =YEAR()

=�RSAVSKR() =�RSAVS() =SYD()

#Division/0! #DIV/0! #DIV/0!

#Saknas! #I/T #N/A

#Namn? #NAVN? #NAME?

#Ogiltigt! #NUM! #NUM!

#V�rdefel! #VERDI! #VALUE!

SANT SANN TRUE

FALSKT USANN FALSE

Back to the top

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:

Back to the top

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

Back to the top

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).

Back to the top


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 () Your browser do not support java!
This page () was downloaded at and was last updated at

visitors since 2001-04-10 (

visitors to the Excel page since 2004-12-25)

Did you see any errors on, or have other comments to this page? Please send Nikolai Sandved an e-mail at [email protected]

"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."

Swedish (Svensk)	Norwegian (Norsk)	Standard English (Engelsk)	Category Office 2003
=ABS()	=ABS()	=ABS()	Math and Trigonometry Functions
=ADRESS()	=ADRESSE()	=ADDRESS()	Lookup and Reference
=ANROPA()	=ANROP()	=CALL()
=ANTAL()	=ANTALL()	=COUNT()	Statistical Functions
=ANTAL.OM()	=ANTALL.HVIS()	=COUNTIF()	Statistical Functions
=ANTALV()	=ANTALLA()	=COUNTA()	Statistical Functions
=BETALNING()	=AVDRAG()	=PMT()	Financial Functions
=AVKORTA()	=AVKORT()	=TRUNC()	Math and Trigonometry Functions
=AVRUNDA()	=AVRUND()	=ROUND()	Math and Trigonometry Functions
=RUNDA.UPP()	=AVRUND.GJELDENDE.MULTIPLUM()	=CEILING()	Math and Trigonometry Functions
=RUNDA.NER()	=AVRUND.GJELDENDE.MULTIPLUM.NED()	=FLOOR()	Math and Trigonometry Functions
=AVRUNDA.NED�T()	=AVRUND.NED()	=ROUNDDOWN()	Math and Trigonometry Functions
=AVRUNDA.UPP�T()	=AVRUND.OPP()	=ROUNDUP()	Math and Trigonometry Functions
=UDDA()	=AVRUND.TIL.ODDETALL()	=ODD()	Math and Trigonometry Functions
=J�MN()	=AVRUND.TIL.PARTALL()	=EVEN()	Math and Trigonometry Functions
=KVADAVV()	=AVVIK.KVADRERT()	=DEVSQ()	Statistical Functions
=BESSELI()	=BESSELI()	=BESSELI()	Engineering Functions
=BETAF�RD()	=BETA.FORDELING()	=BETADIST()	Statistical Functions
=BINOMF�RD()	=BINOM.FORDELING()	=BINOMDIST()	Statistical Functions
=BYT.UT()	=BYTT.UT()	=SUBSTITUTE()	Text and Data Functions
=CELL()	=CELLE()	=CELL()	Information Functions
=DAG()	=DAG()	=DAY()	Date and Time Functions
=DAGAR360()	=DAGER360()	=DAYS360()	Date and Time Functions
=DANTAL()	=DANTALL()	=DCOUNT()
=DANTALV()	=DANTALLA()	=DCOUNTA()
=DATUM()	=DATO()	=DATE()	Date and Time Functions
=DATUMV�RDE()	=DATOVERDI()	=DATEVALUE()	Date and Time Functions
=DB()	=DAVSKR()	=DB()
=DEGAVSKR()	=DEGRAVS()	=DDB()
=DELSUMMA()	=DELSUM()	=SUBTOTAL()
=DELTA()	=DELTA()	=DELTA()	Engineering Functions
=EXTEXT()	=DELTEKST()	=MID()
=DMEDEL()	=DGJENNOMSNITT()	=DAVERAGE()
=DH�MTA()	=DHENT()	=DGET()
=DISC()	=DISC()	=DISC()
=DMAX()	=DMAKS()	=DMAX()
=DMIN()	=DMIN()	=DMIN()
=DOLLARDE()	=DOLLARDE()	=DOLLARDE()
=DPRODUKT()	=DPRODUKT()	=DPRODUCT()
=DSTDAV()	=DSTDAV()	=DSTDEV()
=DSTDAVP()	=DSTDAVP()	=DSTDEVP()
=DSUMMA()	=DSUMMER()	=DSUM()
=DURATION()	=DURATION()	=DURATION()
=DVARIANS()	=DVARIANS()	=DVAR()
=DVARIANSP()	=DVARIANSP()	=DVARP()
=EDATE()	=EDATE()	=EDATE()
=EFFECT()	=EFFECT()	=EFFECT()
=EXAKT()	=EKSAKT()	=EXACT()
=EXP()	=EKSP()	=EXP()
=EXPONF�RD()	=EKSP.FORDELING()	=EXPONDIST()
=ELLER()	=ELLER()	=OR()
=EOMONTH()	=EOMONTH()	=EOMONTH()
=RAL�N()	=ER.AVDRAG()	=ISPMT()
=�RF()	=ERF()	=ISERR()
=ERF()	error message, incredible!	=ERF()
=ERFC()	=ERFC()	=ERFC()
=�RFEL()	=ERFEIL()	=ISERROR()
=�REJTEXT()	=ERIKKETEKST()	=ISNONTEXT()
=�RSAKNAD()	=ERIT()	=ISNA()
=�RLOGISK()	=ERLOGISK()	=ISLOGICAL()
=�RREF()	=ERREF()	=ISREF()
=ERS�TT()	=ERSTATT()	=REPLACE()
=�RTAL()	=ERTALL()	=ISNUMBER()
=�RTEXT()	=ERTEKST()	=ISTEXT()
=�RTOM()	=ERTOM()	=ISBLANK()
=FACTDOUBLE()	=FACTDOUBLE()	=FACTDOUBLE()
=FAKULTET()	=FAKULTET()	=FACT()
=FASTTAL()	=FASTSATT()	=FIXED()
=FEL.TYP()	=FEIL.TYPE()	=ERROR.TYPE()
=FF�RD()	=FFORDELING()	=FDIST()
=FINV()	=FFORDELING.INVERS()	=FINV()
=HITTA()	=FINN()	=FIND()
=LETAKOLUMN()	=FINN.KOLONNE()	=HLOOKUP()
=LETARAD()	=FINN.RAD()	=VLOOKUP()
=FISHER()	=FISHER()	=FISHER()
=FISHERINV()	=FISHERINV()	=FISHERINV()
=F�RSKJUTNING()	=FORSKYVNING()	=OFFSET()
=TECKEN()	=FORTEGN()	=SIGN()
=FREKVENS()	=FREKVENS()	=FREQUENCY()
=FTEST()	=FTEST()	=FTEST()
=FVSCHEDULE()	=FVSCHEDULE()	=FVSCHEDULE()
=GAMMAF�RD()	=GAMMAFORDELING()	=GAMMADIST()
=GAMMAINV()	=GAMMAINV()	=GAMMAINV()
=GAMMALN()	=GAMMALN()	=GAMMALN()
=GCD()	=GCD()	=GCD()
=GESTEP()	=GESTEP()	=GESTEP()
=MEDEL()	=GJENNOMSNITT()	=AVERAGE()
=GEOMEDEL()	=GJENNOMSNITT.GEOMETRISK()	=GEOMEAN()
=HARMMEDEL()	=GJENNOMSNITT.HARMONISK()	=HARMEAN()
=AVERAGEA()	=GJENNOMSNITTA()	=AVERAGEA()
=MEDELAVV()	=GJENNOMSNITTSAVVIK()	=AVEDEV()
=REP()	=GJENTA()	=REPT()
=GRADER()	=GRADER()	=DEGREES()
=KRITBINOM()	=GRENSE.BINOM()	=CRITBINOM()
=HELTAL()	=HELTALL()	=INT()
=H�MTA.PIVOTDATA()	=HENTPIVOTDATA()	=GETPIVOTDATA()
=HEX2BIN()	=HEX2BIN()	=HEX2BIN()
=HEX2DEC()	=HEX2DEC()	=HEX2DEC()
=HEX2OCT()	=HEX2OCT()	=HEX2OCT()
=OM()	=HVIS()	=IF()
=HYPERL�NK()	=HYPERKOBLING()	=HYPERLINK()
=HYPGEOMF�RD()	=HYPGEOM.FORDELING()	=HYPGEOMDIST()
=H�GER()	=H�YRE()	=RIGHT()
=IDAG()	=IDAG()	=TODAY()	Date and Time Functions
=ICKE()	=IKKE()	=NOT()
=IMABS()	=IMABS()	=IMABS()
=IMAGINARY()	=IMAGINARY()	=IMAGINARY()
=IMARGUMENT()	=IMARGUMENT()	=IMARGUMENT()
=IMCONJUGATE()	=IMCONJUGATE()	=IMCONJUGATE()
=IMCOS()	=IMCOS()	=IMCOS()
=IMDIV()	=IMDIV()	=IMDIV()
=IMEXP()	=IMEXP()	=IMEXP()
=IMLN()	=IMLN()	=IMLN()
=IMLOG10()	=IMLOG10()	=IMLOG10()
=IMLOG2()	=IMLOG2()	=IMLOG2()
=IMPOWER()	=IMPOWER()	=IMPOWER()
=IMPRODUCT()	=IMPRODUCT()	=IMPRODUCT()
=IMREAL()	=IMREAL()	=IMREAL()
=IMSIN()	=IMSIN()	=IMSIN()
=IMSQRT()	=IMSQRT()	=IMSQRT()
=IMSUB()	=IMSUB()	=IMSUB()
=IMSUM()	=IMSUM()	=IMSUM()
=INDEX()	=INDEKS()	=INDEX()
=INDIREKT()	=INDIREKTE()	=INDIRECT()
=INFO()	=INFO()	=INFO()
=INTRATE()	=INTRATE()	=INTRATE()
=BETAINV()	=INVERS.BETA.FORDELING()	=BETAINV()
=CHI2INV()	=INVERS.KJI.FORDELING()	=CHIINV()
=IR()	=IR()	=IRR()
=ISEVEN()	=ISEVEN()	=ISEVEN()
=ISODD()	=ISODD()	=ISODD()
=SAKNAS()	=IT()	=NA()
=SAMMANFOGA()	=KJEDE.SAMMEN()	=CONCATENATE()
=CHI2F�RD()	=KJI.FORDELING()	=CHIDIST()
=CHI2TEST()	=KJI.TEST()	=CHITEST()
=KOD()	=KODE()	=CODE()
=KOLUMN()	=KOLONNE()	=COLUMN()
=KOLUMNER()	=KOLONNER()	=COLUMNS()
=KOMBIN()	=KOMBINASJON()	=COMBIN()
=KONFIDENS()	=KONFIDENS()	=CONFIDENCE()
=KORREL()	=KORRELASJON()	=CORREL()
=KOVAR()	=KOVARIANS()	=COVAR()
=TOPPIGHET()	=KURT()	=KURT()
=EXPREGR()	=KURVE()	=LOGEST()
=KVARTIL()	=KVARTIL()	=QUARTILE()
=LCM()	=LCM()	=LCM()
=L�NGD()	=LENGDE()	=LEN()
=LINAVSKR()	=LINAVS()	=SLN()
=LN()	=LN()	=LN()
=LOG()	=LOG()	=LOG()
=LOG10()	=LOG10()	=LOG10()
=LOGINV()	=LOGINV()	=LOGINV()
=LOGNORMF�RD()	=LOGNORMFORD()	=LOGNORMDIST()
=MAXA()	=MAKSA()	=MAXA()
=MDETERM()	=MDETERM()	=MDETERM()
=MDURATION()	=MDURATION()	=MDURATION()
=MEDIAN()	=MEDIAN()	=MEDIAN()
=MIN()	=MIN()	=MIN()
=MINA()	=MINA()	=MINA()
=MINUT()	=MINUTT()	=MINUTE()	Date and Time Functions
=MINVERT()	=MINVERS()	=MINVERSE()
=MMULT()	=MMULT()	=MMULT()
=MODIR()	=MODIR()	=MIRR()
=TYPV�RDE()	=MODUS()	=MODE()
=MROUND()	=MROUND()	=MROUND()
=MULTINOMIAL()	=MULTINOMIAL()	=MULTINOMIAL()
=M�NAD()	=M�NED()	=MONTH()	Date and Time Functions
=N()	=N()	=N()
=MINSTA()	=N.MINST()	=SMALL()
=ST�RSTA()	=N.ST�RST()	=LARGE()
=NEGBINOMF�RD()	=NEGBINOM.FORDELING()	=NEGBINOMDIST()
=NETWORKDAYS()	=NETWORKDAYS()	=NETWORKDAYS()
=NETNUV�RDE()	=NNV()	=NPV()
=NOMINAL()	=NOMINAL()	=NOMINAL()
=NORMF�RD()	=NORMALFORDELING()	=NORMDIST()
=STANDARDISERA()	=NORMALISER()	=STANDARDIZE()
=NORMINV()	=NORMINV()	=NORMINV()
=NU()	=N�()	=NOW()
=NUV�RDE()	=N�VERDI()	=PV()
=OCT2BIN()	=OCT2BIN()	=OCT2BIN()
=OCT2DEC()	=OCT2DEC()	=OCT2DEC()
=OCT2HEX()	=OCT2HEX()	=OCT2HEX()
=ODDFPRICE()	=ODDFPRICE()	=ODDFPRICE()
=ODDFYIELD()	=ODDFYIELD()	=ODDFYIELD()
=ODDLPRICE()	=ODDLPRICE()	=ODDLPRICE()
=ODDLYIELD()	=ODDLYIELD()	=ODDLYIELD()
=OCH()	=OG()	=AND()
=OMR�DEN()	=OMR�DER()	=AREAS()
=UPPH�JT.TILL()	=OPPH�YD.I()	=POWER()
=PEARSON()	=PEARSON()	=PEARSON()
=PERIODER()	=PERIODER()	=NPER()
=PERMUT()	=PERMUTER()	=PERMUT()
=PERCENTIL()	=PERSENTIL()	=PERCENTILE()
=PI()	=PI()	=PI()
=POISSON()	=POISSON()	=POISSON()
=PRICE()	=PRICE()	=PRICE()
=PRICEDISC()	=PRICEDISC()	=PRICEDISC()
=PRICEMAT()	=PRICEMAT()	=PRICEMAT()
=PRODUKT()	=PRODUKT()	=PRODUCT()
=PREDIKTION()	=PROGNOSE()	=FORECAST()
=PROCENTRANG()	=PROSENTDEL()	=PERCENTRANK()
=QUOTIENT()	=QUOTIENT()	=QUOTIENT()
=RAD()	=RAD()	=ROW()
=RADER()	=RADER()	=ROWS()
=RADIANER()	=RADIANER()	=RADIANS()
=RANDBETWEEN()	=RANDBETWEEN()	=RANDBETWEEN()
=RANG()	=RANG()	=RANK()
=RBETALNING()	=RAVDRAG()	=IPMT()
=RECEIVED()	=RECEIVED()	=RECEIVED()
=REGISTRERA.ID()	=REGISTRER.ID()	=REGISTER.ID()
=ST�DA()	=RENSK()	=CLEAN()
=R�NTA()	=RENTE()	=RATE()
=REST()	=REST()	=MOD()
=REGR()	=RETTLINJE()	=LINEST()
=RKV()	=RKVADRAT()	=RSQ()
=ROMERSK()	=ROMERTALL()	=ROMAN()
=ROT()	=ROT()	=SQRT()
=PASSA()	=SAMMENLIGNE()	=MATCH()
=SANT()	=SANN()	=TRUE()
=SANNOLIKHET()	=SANNSYNLIG()	=PROB()
=SEKUND()	=SEKUND()	=SECOND()
=SERIESSUM()	=SERIESSUM()	=SERIESSUM()
=SIN()	=SIN()	=SIN()
=SINH()	=SINH()	=SINH()
=SNEDHET()	=SKJEVFORDELING()	=SKEW()
=SK�RNINGSPUNKT()	=SKJ�RINGSPUNKT()	=INTERCEPT()
=SLUTV�RDE()	=SLUTTVERDI()	=FV()
=LETAUPP()	=SL�.OPP()	=LOOKUP()
=GEMENER()	=SM�()	=LOWER()
=SQRTPI()	=SQRTPI()	=SQRTPI()
=STDFELYX()	=STANDARDFEIL()	=STEYX()
=STDAV()	=STDAV()	=STDEV()
=STDAVP()	=STDAVP()	=STDEVP()
=STDEVA()	=STDAVVIKA()	=STDEVA()
=STDEVPA()	=STDAVVIKPA()	=STDEVPA()
=LUTNING()	=STIGNINGSTALL()	=SLOPE()
=INITIAL()	=STOR.FORBOKSTAV()	=PROPER()
=VERSALER()	=STORE()	=UPPER()
=MAX()	=ST�RST()	=MAX()
=SUMMA()	=SUMMER()	=SUM()
=SUMMA.OM()	=SUMMERHVIS()	=SUMIF()
=KVADRATSUMMA()	=SUMMERKVADRAT()	=SUMSQ()
=PRODUKTSUMMA()	=SUMMERPRODUKT()	=SUMPRODUCT()
=SUMMAX2MY2()	=SUMMERX2MY2()	=SUMX2MY2()
=SUMMAX2PY2()	=SUMMERX2PY2()	=SUMX2PY2()
=SUMMAXMY2()	=SUMMERXMY2()	=SUMXMY2()
=S�K()	=S�K()	=SEARCH()
=T()	=T()	=T()
=TAN()	=TAN()	=TAN()
=TANH()	=TANH()	=TANH()
=TBILLEQ()	=TBILLEQ()	=TBILLEQ()
=TBILLPRICE()	=TBILLPRICE()	=TBILLPRICE()
=TBILLYIELD()	=TBILLYIELD()	=TBILLYIELD()
=TECKENKOD()	=TEGNKODE()	=CHAR()
=TEXT()	=TEKST()	=TEXT()
=ANTAL.TOMMA()	=TELLBLANKE()	=COUNTBLANK()
=TF�RD()	=TFORDELING()	=TDIST()
=KLOCKSLAG()	=TID()	=TIME()	Date and Time Functions
=TIDV�RDE()	=TIDSVERDI()	=TIMEVALUE()	Date and Time Functions
=SLUMP()	=TILFELDIG()	=RAND()
=TIMME()	=TIME()	=HOUR()	Date and Time Functions
=TINV()	=TINV()	=TINV()
=TRANSPONERA()	=TRANSPONER()	=TRANSPOSE()
=TREND()	=TREND()	=TREND()
=RENSA()	=TRIMME()	=TRIM()
=TRIMMEDEL()	=TRIMMET.GJENNOMSNITT()	=TRIMMEAN()
=TTEST()	=TTEST()	=TTEST()
=VECKODAG()	=UKEDAG()	=WEEKDAY()	Date and Time Functions
=FALSKT()	=USANN()	=FALSE()
=VALUTA()	=VALUTA()	=DOLLAR()
=VARIANS()	=VARIANS()	=VAR()
=VARA()	=VARIANSA()	=VARA()
=VARIANSP()	=VARIANSP()	=VARP()
=VARPA()	=VARIANSPA()	=VARPA()
=EXPTREND()	=VEKST()	=GROWTH()
=V�LJ()	=VELG()	=CHOOSE()
=V�NSTER()	=VENSTRE()	=LEFT()
=TEXTNUM()	=VERDI()	=VALUE()
=VDEGRAVSKR()	=VERDIAVS()	=VDB()
=V�RDETYP()	=VERDITYPE()	=TYPE()
=WEEKNUM()	=WEEKNUM()	=WEEKNUM()	Date and Time Functions
=WEIBULL()	=WEIBULL.FORDELING()	=WEIBULL()
=WORKDAY()	=WORKDAY()	=WORKDAY()	Date and Time Functions
=XIRR()	=XIRR()	=XIRR()
=XL97()	=XL97()	=XL97()
=XNPV()	=XNPV()	=XNPV()
=YEARFRAC()	=YEARFRAC()	=YEARFRAC()
=YIELD()	=YIELD()	=YIELD()
=YIELDDISC()	=YIELDDISC()	=YIELDDISC()
=YIELDMAT()	=YIELDMAT()	=YIELDMAT()
=ZTEST()	=ZTEST()	=ZTEST()
=�R()	=�R()	=YEAR()
=�RSAVSKR()	=�RSAVS()	=SYD()



#Division/0!	#DIV/0!	#DIV/0!
#Saknas!	#I/T	#N/A
#Namn?	#NAVN?	#NAME?
#Ogiltigt!	#NUM!	#NUM!
#V�rdefel!	#VERDI!	#VALUE!
SANT	SANN	TRUE
FALSKT	USANN	FALSE