 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
ISO8601: 2000 is becoming mandatory in the European
Union and will become more commonly
used throughout the World.
The Standard, first issued as long ago as 1986, prescribes amongst other
things,
1. Dates must be represented in either:
a. separated form of yyyy-mm-dd (eg 2005-03-30 for
30-03-2005)
b. separated form of yyyy/mm/dd (eg
2005/03/30 for 30-03-2005)
2. Week numbering should use the
following algorithm that can be defined in one of two ways:
a. Week 1 starts on the Monday of the week in which 4th January falls.
b. Week 1 starts on the Monday of the week that contains the first Thursday
of the calendar year.
For Excel users, the main difficulties of
the Standard are:
a. non-familiarity with
the system of date representation.
b. the fact that
there is no built in or ATP function before Excel 2010 that supports the ISO
week number
There are four main advantages of the ISO date representation:
1. Old hands at computing will readily
appreciate that a sorted date representation yyyy-mm-dd
or yyyy/mm/dd will produces a correctly ordered series of
dates. The fact that the numbers
are not a complete
numerical sequence does not adversely impact upon the sequence.
2. By adopting a numeric representation, there can be no language
based confusion that results from
use of long or
short alphabetical names of the Months.
3. There will be
an end to the confusion that results from (predominantly US) dates of
12-11-2005
for 11th December 2005 compared to the
(predominantly European) form that interprets
12-11-2005 as 12th November 2005.
4. By using four
digits for the year number, there will be no confusion as which Century the
date falls.
The week numbering algorithm looks strange. It means that
Week 1 will not start on 1st January.
Week 1 may start as early as 28th
December in the preceding Calendar Year and that Weeks 52
or 53 may
extend into the next Calendar Year as far as 3rd January.
The
advantage of the ISO week number system are:
1.
Every week will be of equal length. Other systems produce weeks with less
than 7 days at the beginning
and / or the end.
2. Every week will start on a Monday. Day 3 of Week 7, will
always be a Wednesday.
3. There will always be 52 or 53
ISO weeks in a year. Other systems can create peculiar years where there
can be 54 weeks.
Excel related aspects of date
representation:
1. All versions of Excel
in all language editions from at least 1997 have yyyy-mm-dd as one
of the standard date formats.
2. Where dates are
provided as strings in the various date functions, use of the string format
yyyy-mm-dd will always be interpreted correctly
irrespective of the Regional Settings and the
double
digit year interpretation setting.
With all other string inputs to
date functions, the result will vary or will fail to be interpreted as the
date intended. Since Excel 2000, Help has advised against using string
arguments for dates in Date functions.
The reason for that advice is
different interpretations depending upon Regional Settings and double digit
year interpretation setting.
If you use the ISO separated date
format, you can reject that advice and use (eg)
=WEEKDAY("2005-02-23",1)
Rather than:
=WEEKDAY(DATE(2005,02,23),1)
Strings may also be constructed using the INDIRECT
function. If you use any other string form in this way, it will all end in
tears if Regional settings or Double Date interpretation settings on the
computer that the workbook is opened are different from the ones that
existed on the original computer the workbook was built on.Not much use on
its own! But the form represents a useful and easy way to "hard code" dates
into formulas.
3. Entry of a date using
yyyy-mm-dd will always be correctly interpreted as the date intended by the
user.
Other date entry forms may be interpreted
differently depending upon Regional Settings and double
digit year interpretation settings.
In many cases the entry will be
converted to a text entry and as a result:
a. It will not often be
immediately obvious that the entry is not regarded by Excel as a date.
b. If used in a subsequent date calculation, the date serial number will be
regarded as 0, and will be
interpreted as 31-December-1899.
Consider the entry of 03-07-05. This has one of one of 6
possible interpretations depending upon Regional Settings and double digit
year interpretation setting:
03 July 2005
03 July 1905
07
March 2005
07 March 1905
05 July 2003
05 July 1903
4. One problem that exists with the TEXT function.
The format string will not translate if the workbook is opened on a
different language version of Excel.
For example:
English
language Version entry:
="Today is
"&TEXT(TODAY(),"yyyy-mm-dd")
In English language version the
formula returns:
Today is 2005-02-23
Fails when the when workbook is open in Dutch language version (year = jaar
"jjjj-mm-dd").
With a Dutch language version the formula will return:
Today is yyyy-02-23
The same effect
arises when you use TEXT in the Dutch language version of Excel and then try
and open the workbook with an English language version. These errors exist
whatever date string is used and is not peculiar to ISO date strings.
5. There are only two date entry forms that will be
unequivocally interpreted as the intended date
by all
language and all versions of Excel:
yyyy-mm-dd
and
yyyy/mm/dd
But why use the "/" form when the
ISO Standard agreed separator is "-"?
6. If you
pre-format a range of cells as text, you can enter all dates as yyyy-mm-dd
including dates that
are before 1900-01-01 (Or
1904-01-02 if you use the 1904 Date System).
This
allows subsequent sorting of the dates into date order. Without
pre-formatting as text, sorting
will sort date serial
numbers in date order and (pre-1900) text dates in alphanumeric order.
Here are the UDFs for the two most useful functions that facilitate the
implementation of the Standard:
For a example how to use the UDFs
download the example workbook.
Here’s the UDF for finding
ISOYEARSTART :
Public Function ISOYEARSTART(WhichYear As Integer) As Date
' First published by John Green, Excel MVP, Sydney, Australia
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARSTART = NewYear - WeekDay
Else
ISOYEARSTART = NewYear - WeekDay + 7
End If
End Function
And the most efficient UDF for determining the ISO Week number from a date appears to be:
Public Function IsoWeekNumber(d1 As Date) As Integer
' Attributed to Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
IsoWeekNumber = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function
The workbook contains comprehensive formulas that assist in ISO
implementation, an ISO weeknumber calendar creator, and examples of use of
two VBA User Defined Functions.
Download workbook
Note however, that the operation of the
workbook's User Defined Functions requires that the Security
settings
should be set at Medium or Low. This is not usually recommended except for
opening workbooks
from known and trusted sources.
John Green, Excel MVP, Sydney, Australia. John was the originator the
ISOYEARSTART function above.
Our formulas for determining ISO Year Start
are derived from that function. Daniel Maher (Who tends to post under the
title "Daniel M"). Similarly, our formulas for week numbering are derived
from that function.