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

ISO 8601 Date and Time - Converting and implementing

This Page is dedicated to the International Standard ISO 8601 standard and how it can be implemented in various systems

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

First you should get familiar with the ISO 8601 standard.

* ISO 8601:2000 Second Edition [Final Draft 2000-12-15] (185K bytes; last changed: 2006-01-13T20:30:15 CET).

* ISO 8601:1988 First Edition [Original Version 1988-06-15] (PDF 1.2MB) (1.2M bytes; last changed: 2006-01-13T20:30:13 CET).

* More documents at the ISO8601 Group.

You can find a very good summary at http://www.cl.cam.ac.uk/~mgk25/iso-time.html

A discussion list (newsgroup) can be found at http://groups.yahoo.com/group/iso8601/

A good collection of sources at the ISO 8601 Standard section of the Open Directory Project http://dmoz.org/Science/Reference/Standards/Individual_Standards/ISO_8601/

Another good source at http://www.saqqara.demon.co.uk/datefmt.htm

or at http://www.jat.org/jtt/datetime.html

or at http://www.qsl.net/g1smd/
where you can find ISO 8601:1988 Standard Acrobat Format and ISO 8601 Revised Draft 1997 Acrobat Format.

The Mathematics of the ISO 8601 Calendar at the site http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm

C code for calculating the day of the week and leap year at the site http://www.ietf.org/internet-drafts/draft-ietf-impp-datetime-05.txt

Algorithm for Converting Gregorian Dates to ISO 8601 Week Date http://personal.ecu.edu/mccartyr/ISOwdALG.txt

Code examples at http://www.cs.tut.fi/~jkorpela/iso8601.html or http://www.malibutelecom.com/yucca/iso8601.html

A superb discussion about use of the ISO 8601, ISO 31, ISO 4217 (Codes for the Representation of Currencies and Funds) and ISO 3166 (Country codes), on the page http://www.betweenborders.com/international/index.html

take my poll at: ISO 8601 Poll:

Implement the date and time on the ISO-format YYYY-MM-DDThh:mm:ss in

* C/C++

Use the string

Code
"%Y-%m-%dT%H:%M:%S"

as the format specifier for strftime()

* Shell script

Code
date "+%Y-%m-%dT%H:%M:%S"

* CGI - Not yet -

* Common Lisp

Thanks to Thomas Russ tar AT ISI.EDU for this information

Code (I've not tested it myself)
(defun format-iso8601-time (time-value &optional include-timezone-p) "Formats a universal time TIME-VALUE in ISO 8601 format, with the time zone included if INCLUDE-TIMEZONE-P is non-NIL" (flet ((format-iso8601-timezone (zone) (if (zerop zone) "Z" (multiple-value-bind (h m) (truncate (abs zone) 1.0) ;; Tricky. Sign of time zone is reversed in ISO 8601 ;; relative to Common Lisp convention! (format nil "~:[+~;-~]~2,'0D:~2,'0D" (> zone 0) h (round m)))))) (multiple-value-bind (second minute hour day month year dow dst zone) (decode-universal-time time-value) (declare (ignore dow dst)) (format nil "~4,'0D-~2,'0D-~2,'0DT~2,'0D:~2,'0D:~2,'0D~:[~~;~A~]" year month day hour minute second include-timezone-p (format-iso8601-timezone zone))))) (defun parse-iso8601-time (time-string) "Parses an ISO 8601 format string and returns the universal time" (flet ((parse-delimited-string (string delimiter n) ;; Parses a delimited string and returns a list of ;; n integers found in that string. (let ((answer (make-list n :initial-element 0))) (loop for i upfrom 0 for start = 0 then (1+ end) for end = (position delimiter string :start (1+ start)) do (setf (nth i answer) (parse-integer (subseq string start end))) when (null end) return t) (values-list answer))) (parse-fixed-field-string (string field-sizes) ;; Parses a string with fixed length fields and returns ;; a list of integers found in that string. (let ((answer (make-list (length field-sizes) :initial-element 0))) (loop with len = (length string) for start = 0 then (+ start field-size) for field-size in field-sizes for i upfrom 0 while (< start len) do (setf (nth i answer) (parse-integer (subseq string start (+ start field-size))))) (values-list answer)))) (flet ((parse-iso8601-date (date-string) (let ((hyphen-pos (position #\- date-string))) (if hyphen-pos (parse-delimited-string date-string #\- 3) (parse-fixed-field-string date-string '(4 2 2))))) (parse-iso8601-timeonly (time-string) (let ((colon-pos (position #\: time-string)) (zone-pos (or (position #\- time-string) (position #\+ time-string))) (timeonly-string (subseq time-string 0 zone-pos)) (zone-string (when zone-pos (subseq time-string (1+ zone-pos)))) (time-zone nil)) (when zone-pos (multiple-value-bind (zone-h zone-m) (parse-delimited-string zone-string #\: 2) (setq time-zone (+ zone-h (/ zone-m 60))) (when (char= (char time-string zone-pos) #\-) (setq time-zone (- time-zone))))) (multiple-value-bind (hh mm ss) (if colon-pos (parse-delimited-string timeonly-string #\: 3) (parse-fixed-field-string timeonly-string '(2 2 2))) (values hh mm ss time-zone))))) (let ((time-separator (position #\T time-string))) (multiple-value-bind (year month date) (parse-iso8601-date (subseq time-string 0 time-separator)) (if time-separator (multiple-value-bind (hh mm ss zone) (parse-iso8601-timeonly (subseq time-string (1+ time-separator))) (if zone ;; Tricky: Sign of time zone is reversed in ISO 8601 ;; relative to Common Lisp convention! (encode-universal-time ss mm hh date month year (- zone)) (encode-universal-time ss mm hh date month year))) (encode-universal-time 0 0 0 date month year)))))))

Code (I've not tested it myself)

 (defun format-iso8601-time (time-value &optional include-timezone-p)
    "Formats a universal time TIME-VALUE in ISO 8601 format, with the time zone included if INCLUDE-TIMEZONE-P is non-NIL"
    (flet ((format-iso8601-timezone (zone)
             (if (zerop zone)
               "Z"
               (multiple-value-bind (h m) (truncate (abs zone) 1.0)
                 ;; Tricky.  Sign of time zone is reversed in ISO 8601
                 ;; relative to Common Lisp convention!
                 (format nil "~:[+~;-~]~2,'0D:~2,'0D"
                         (> zone 0) h (round m))))))
    (multiple-value-bind (second minute hour day month year dow dst zone)
      (decode-universal-time time-value)
      (declare (ignore dow dst))
      (format nil "~4,'0D-~2,'0D-~2,'0DT~2,'0D:~2,'0D:~2,'0D~:[~*~;~A~]"
              year month day hour minute second
              include-timezone-p (format-iso8601-timezone zone)))))

(defun parse-iso8601-time (time-string)
  "Parses an ISO 8601 format string and returns the universal time"
  (flet ((parse-delimited-string (string delimiter n)
           ;; Parses a delimited string and returns a list of
           ;; n integers found in that string.
           (let ((answer (make-list n :initial-element 0)))
             (loop for i upfrom 0
                   for start = 0 then (1+ end)
                   for end = (position delimiter string :start (1+ start))
                   do (setf (nth i answer)
                            (parse-integer (subseq string start end)))
                   when (null end) return t)
             (values-list answer)))
         (parse-fixed-field-string (string field-sizes)
           ;; Parses a string with fixed length fields and returns
           ;; a list of integers found in that string.
           (let ((answer (make-list (length field-sizes) :initial-element 0)))
             (loop with len = (length string)
                   for start = 0 then (+ start field-size)
                   for field-size in field-sizes
                   for i upfrom 0
                   while (< start len)
                   do (setf (nth i answer)
                            (parse-integer (subseq string start (+ start field-size)))))
             (values-list answer))))
    (flet ((parse-iso8601-date (date-string)
             (let ((hyphen-pos (position #\- date-string)))
               (if hyphen-pos
                 (parse-delimited-string date-string #\- 3)
                 (parse-fixed-field-string date-string '(4 2 2)))))
           (parse-iso8601-timeonly (time-string)
             (let* ((colon-pos (position #\: time-string))
                    (zone-pos (or (position #\- time-string)
                                  (position #\+ time-string)))
                    (timeonly-string (subseq time-string 0 zone-pos))
                    (zone-string (when zone-pos (subseq time-string (1+ zone-pos))))
                    (time-zone nil))
               (when zone-pos
                 (multiple-value-bind (zone-h zone-m)
                                      (parse-delimited-string zone-string #\: 2)
                   (setq time-zone (+ zone-h (/ zone-m 60)))
                   (when (char= (char time-string zone-pos) #\-)
                     (setq time-zone (- time-zone)))))
               (multiple-value-bind (hh mm ss)
                                    (if colon-pos
                                      (parse-delimited-string timeonly-string #\: 3)
                                      (parse-fixed-field-string timeonly-string '(2 2 2)))
                   (values hh mm ss time-zone)))))
    (let ((time-separator (position #\T time-string)))
      (multiple-value-bind (year month date)
                           (parse-iso8601-date
                            (subseq time-string 0 time-separator))
        (if time-separator
          (multiple-value-bind (hh mm ss zone)
                               (parse-iso8601-timeonly
                                (subseq time-string (1+ time-separator)))
            (if zone
              ;; Tricky:  Sign of time zone is reversed in ISO 8601
              ;; relative to Common Lisp convention!
              (encode-universal-time ss mm hh date month year (- zone))
              (encode-universal-time ss mm hh date month year)))
          (encode-universal-time 0 0 0 date month year)))))))

* Java

Thanks to sng AT automotive.com for this information

Code for the Import section
import java.text.SimpleDateFormat; import java.util.Date;

And in the Code
Date now = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); sdf.format( now );

* Perl

Thanks to morgan.nilsson AT ateles.se for this information

Code
use POSIX qw(strftime); my $datetime = strftime("%Y-%m-%dT%H:%M:%S", localtime());

* PHP

Code
<?php /* * Code to ekstract the ISO Week format YYYY-Www-D from a given date * Here YYYY is the ISO year, ww the ISO week, and D day of week where 1-Monday and 7-Sunday * For example will 1999-01-03 return 1998-W52-7 * See more about ISO 8601 and how to treat weeks at http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm * and http://www.pvv.org/~nsaa/ISO8601.html * * Code for PHP version 4.1 to PHP 5.0 * I've used the date parameter 'W' first available from PHP 4.1 * From PHP 5.1 the year test is not necessary. Use the parameter 'o' since this return the ISO year * See http://se2.php.net/manual/en/function.date.php for all the date parameters */ // border test cases for week calculations //if // $datetext = "28 December 2003"; //2003-W52-7 // $datetext = "29 December 2003"; //2004-W01-1 //elseif $datetext = "3 January 1999"; //1998-W53-7 //$datetext = "4 January 1999"; //1999-W01-1 //$datetext = "2 January 2000"; //1999-W52-7 //$datetext = "3 January 2000"; //2000-W01-1 // //Adds leading 0 to the week number $ISOweek = substr('0' . date('W',strtotime($datetext)) , -2); $ISOday = date('w',strtotime($datetext)); //make sunday the last day in week, not the first as returned by the parameter 'w' if ($ISOday == 0) {$ISOday = '7';} //Checks if the ISO Week 01 falls in desember the year before if (((int)date('d',strtotime($datetext))>=29) && ((int)date('m',strtotime($datetext))==12) && ((int)date('W',strtotime($datetext))==1)) { $ISOyear = date('Y',strtotime($datetext)) + 1; //Checks if the ISO Week 52 or 53 falls in january the year after } elseif (((int)date('d',strtotime($datetext))<=3) && ((int)date('m',strtotime($datetext))==1) && ((int)date('W',strtotime($datetext))>=52)) { $ISOyear = date('Y',strtotime($datetext)) - 1; } else { $ISOyear = date('Y',strtotime($datetext)); } $datetext = date('Y-m-d',strtotime($datetext)); $ISOyearweek = $ISOyear . "-W" . $ISOweek; $ISOywday = $ISOyear . "-W" . $ISOweek . "-" . $ISOday; echo "<PRE> Date : $datetext Week : $ISOyearweek Full week : $ISOywday </PRE>"; ?>

Code

<?php
/*
 * Code to ekstract the ISO Week format YYYY-Www-D from a given date
 * Here YYYY is the ISO year, ww the ISO week, and D day of week where 1-Monday and 7-Sunday
 * For example will 1999-01-03 return 1998-W52-7
 * See more about ISO 8601 and how to treat weeks at http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm
 * and http://www.pvv.org/~nsaa/ISO8601.html
 *
 * Code for PHP version 4.1 to PHP 5.0
 * I've used the date parameter 'W' first available from PHP 4.1
 * From PHP 5.1 the year test is not necessary. Use the parameter 'o' since this return the ISO year
 * See http://se2.php.net/manual/en/function.date.php for all the date parameters
 */
// border test cases for week calculations
//if
// $datetext = "28 December 2003"; //2003-W52-7
// $datetext = "29 December 2003"; //2004-W01-1
//elseif
$datetext = "3 January 1999"; //1998-W53-7
//$datetext = "4 January 1999"; //1999-W01-1
//$datetext = "2 January 2000"; //1999-W52-7
//$datetext = "3 January 2000"; //2000-W01-1
//
//Adds leading 0 to the week number
$ISOweek = substr('0' . date('W',strtotime($datetext)) , -2);
$ISOday = date('w',strtotime($datetext));
//make sunday the last day in week, not the first as returned by the parameter 'w'
if ($ISOday == 0) {$ISOday = '7';}
//Checks if the ISO Week 01 falls in desember the year before
if (((int)date('d',strtotime($datetext))>=29) && ((int)date('m',strtotime($datetext))==12) && ((int)date('W',strtotime($datetext))==1)) {
$ISOyear = date('Y',strtotime($datetext)) + 1;
//Checks if the ISO Week 52 or 53 falls in january the year after
} elseif (((int)date('d',strtotime($datetext))<=3) && ((int)date('m',strtotime($datetext))==1) && ((int)date('W',strtotime($datetext))>=52)) {
$ISOyear = date('Y',strtotime($datetext)) - 1;
} else {
$ISOyear = date('Y',strtotime($datetext));
}

$datetext = date('Y-m-d',strtotime($datetext));
$ISOyearweek = $ISOyear . "-W" . $ISOweek;
$ISOywday = $ISOyear . "-W" . $ISOweek . "-" . $ISOday;

echo "<PRE>
   Date      : $datetext
   Week      : $ISOyearweek
   Full week : $ISOywday
</PRE>";
?>

Results
Date : 1999-01-03 Week : 1998-W53 Full week : 1998-W53-7

* Siebel eScript

Code
// ISODate on the ISO 8601:2000 Date and Time format YYYY-MM-DDThh:mm:ss,sss // ISO 8601:1997 draft at ftp://ftp.qsl.net/pub/g1smd/8601v03.pdf // Coordinated Universial Time (UTC) time -> YYYY-MM-DDThh:mm:ss,sssZ var CurDat = new Date; var MM = CurDat.getUTCMonth() + 1; MM = "0" + MM; var DD = "0" + CurDat.getUTCDate(); var hh = "0" + CurDat.getUTCHours(); var mm = "0" + CurDat.getUTCMinutes(); var ss = "0" + CurDat.getUTCSeconds(); var ISOUTCDate = CurDat.getUTCFullYear() + "-" + MM.substring(MM.length-2) + "-" + DD.substring(DD.length-2) + "T" + hh.substring(hh.length-2) + ":" + mm.substring(mm.length-2) + ":" + ss.substring(ss.length-2) + "," + CurDat.getUTCMilliseconds() + "Z" // Local time -> YYYY-MM-DDThh:mm:ss,sss+-hh:mm var CurDat = new Date; var MM = CurDat.getMonth() + 1; MM = "0" + MM; var DD = "0" + CurDat.getDate(); var hh = "0" + CurDat.getHours(); var mm = "0" + CurDat.getMinutes(); var ss = "0" + CurDat.getSeconds(); var TZhh = Math.round(CurDat.getTimezoneOffset()/60); //Hours from UTC time var TZSign =""; if ( Math.abs(TZhh)==TZhh ) { TZSign = "+"; } else { TZSign = "-"; }; TZhh = TZhh.toString(); // Convert to string "-2"/"+13" TZhh = "0" + TZhh.substring(TZhh.length-1); // remove sign and add a zero "02"/"013" var TZmm = CurDat.getTimezoneOffset() - 60*Math.round(CurDat.getTimezoneOffset()/60); //Minute part from UTC time TZmm = "0" + TZmm; var ISODate = CurDat.getFullYear() + "-" + MM.substring(MM.length-2) + "-" + DD.substring(DD.length-2) + "T" + hh.substring(hh.length-2) + ":" + mm.substring(mm.length-2) + ":" + ss.substring(ss.length-2) + "," + CurDat.getMilliseconds() + TZSign + TZhh.substring(TZhh.length-2) + ":" + TZmm.substring(TZmm.length-2);

Code

   // ISODate on the ISO 8601:2000 Date and Time format YYYY-MM-DDThh:mm:ss,sss
   // ISO 8601:1997 draft at ftp://ftp.qsl.net/pub/g1smd/8601v03.pdf

   // Coordinated Universial Time (UTC) time -> YYYY-MM-DDThh:mm:ss,sssZ
   var CurDat = new Date;
   var MM = CurDat.getUTCMonth() + 1;
   MM = "0" + MM;
   var DD = "0" + CurDat.getUTCDate();
   var hh = "0" + CurDat.getUTCHours();
   var mm = "0" + CurDat.getUTCMinutes();
   var ss = "0" + CurDat.getUTCSeconds();
   var ISOUTCDate = CurDat.getUTCFullYear() + "-" + MM.substring(MM.length-2) +
      "-" + DD.substring(DD.length-2) + "T" + hh.substring(hh.length-2) +
      ":" + mm.substring(mm.length-2) + ":" + ss.substring(ss.length-2) +
      "," + CurDat.getUTCMilliseconds() + "Z"

   // Local time -> YYYY-MM-DDThh:mm:ss,sss+-hh:mm
   var CurDat = new Date;
   var MM = CurDat.getMonth() + 1;
   MM = "0" + MM;
   var DD = "0" + CurDat.getDate();
   var hh = "0" + CurDat.getHours();
   var mm = "0" + CurDat.getMinutes();
   var ss = "0" + CurDat.getSeconds();
   var TZhh = Math.round(CurDat.getTimezoneOffset()/60); //Hours from UTC time
   var TZSign ="";
   if ( Math.abs(TZhh)==TZhh ) { TZSign = "+"; } else { TZSign = "-"; };
   TZhh = TZhh.toString(); // Convert to string "-2"/"+13"
   TZhh = "0" + TZhh.substring(TZhh.length-1); // remove sign and add a zero "02"/"013"
   var TZmm = CurDat.getTimezoneOffset() - 60*Math.round(CurDat.getTimezoneOffset()/60); //Minute part from UTC time
   TZmm = "0" + TZmm;
   var ISODate = CurDat.getFullYear() + "-" + MM.substring(MM.length-2) +
      "-" + DD.substring(DD.length-2) + "T" + hh.substring(hh.length-2) +
      ":" + mm.substring(mm.length-2) + ":" + ss.substring(ss.length-2) +
      "," + CurDat.getMilliseconds() + TZSign + TZhh.substring(TZhh.length-2) +
      ":" + TZmm.substring(TZmm.length-2);

* SQL - PL/SQL and T-SQL

PL/SQL (Oracle)
Ex.:

Code
SELECT TO_CHAR(sysdate, 'YYYYMMDD"T"HH24MISS') "ISO Date & Time", TO_CHAR(sysdate, 'YYYY-MM-DD"T"HH24:MI:SS') "ISO Date & Time Ext", TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD'), 'IYYY"W"IW') \|\| TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD')-1,'D') "ISO Week", TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD'),'IYYY"-W"IW"-"') \|\| TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD')-1,'D') "ISO Week Ext" FROM dual

Code

       SELECT
         TO_CHAR(sysdate, 'YYYYMMDD"T"HH24MISS') "ISO Date & Time",
         TO_CHAR(sysdate, 'YYYY-MM-DD"T"HH24:MI:SS') "ISO Date & Time Ext",
         TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD'), 'IYYY"W"IW') ||
           TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD')-1,'D') "ISO Week",
         TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD'),'IYYY"-W"IW"-"') ||
           TO_CHAR(TO_DATE('2003-12-29', 'YYYY-MM-DD')-1,'D') "ISO Week Ext"
       FROM dual

Results
ISO Date & Time ISO Date & Time Ext ISO Week ISO Week Ext --------------- ------------------- -------- ------------ 20030129T161157 2003-01-29T16:11:57 2004W011 2004-W01-1

T-SQL (Sybase)
Ex.:

Code
SELECT DISTINCT CONVERT(CHAR(10), table.datefield, 112) 'YYYYMMDD', SUBSTRING(CONVERT(CHAR(10), table.datefield, 112),1,4) + "-" + SUBSTRING(CONVERT(CHAR(10), table.datefield, 112),5,2) + "-" + SUBSTRING(CONVERT(CHAR(10), table.datefield, 112),7,2) + "T" + CONVERT(CHAR(10), table.datefield, 108) 'YYYY-MM-DDThh:mm:ss' FROM table

Code

       SELECT DISTINCT
         CONVERT(CHAR(10), table.datefield, 112) 'YYYYMMDD',
         SUBSTRING(CONVERT(CHAR(10), table.datefield, 112),1,4) + "-" +
         SUBSTRING(CONVERT(CHAR(10), table.datefield, 112),5,2) + "-" +
         SUBSTRING(CONVERT(CHAR(10), table.datefield, 112),7,2) + "T" +
         CONVERT(CHAR(10), table.datefield, 108) 'YYYY-MM-DDThh:mm:ss'
       FROM table

Results
YYYYMMDD YYYY-MM-DDThh:mm:ss 20000907 2000-09-07T21:56:54

An alternative in T-SQL for Sybase. Thanks to morgan.nilsson AT ateles.se for this information:

Code
--- 102 => yyyy.mm.dd stuff(stuff(convert(varchar, datefield, 102), 5, 1, '-'), 8, 1, '-') + 'T' + convert(char(8), datefield, 108),

Only accesses the datefield twice.

* VB - Visual Basic / VBA - Visual Basic for Application

Also see my Excel page for implementations in Excel (xls).
Code

'Returns current system date and time in the ISO 8601 format YYYY-MM-DDThh:mm:ss. ISODateandtime = Format$(Now(), "yyyy-mm-ddTHH:MM:SS")

Code
'Returns current system date and time in the ISO 8601 format YYYY-MM-DDThh:mm:ss. ISODateandtime = Format$(Now(), "yyyy-mm-ddTHH:MM:SS")

Thanks to Michael.Rosqvist AT lansforsakringar.se
Code

' Returns current system date in the ISO 8601 date format. ISODate = Format(Date, "yyyy-mm-dd") ' Returns current system time in the ISO 8601 time format. ISOTime = Format(Time, "hh:mm:ss") ' Returns current system date and time in the ISO 8601 format. ISODateandTime = ISODate & "T" & ISOTime

Code
' Returns current system date in the ISO 8601 date format. ISODate = Format(Date, "yyyy-mm-dd") ' Returns current system time in the ISO 8601 time format. ISOTime = Format(Time, "hh:mm:ss") ' Returns current system date and time in the ISO 8601 format. ISODateandTime = ISODate & "T" & ISOTime

* sqlSERVER 2000

Thanks to Michael.Rosqvist AT lansforsakringar.se
Code

SELECT CONVERT(CHAR(23), current_timestamp, 126)

Code
SELECT CONVERT(CHAR(23), current_timestamp, 126)

* Windows 95/97/2000/NT

Code
Start->Settings->Control Panel->Regional Settings->Date Short Date style : yyyy-MM-dd Date separator : - Start->Settings->Control Panel->Regional Settings->Time Time style : HH:mm:ss Time separator : : and no AM or PM symbols (Uppercase HH - 24 Hour)

Code

     Start->Settings->Control Panel->Regional Settings->Date
       Short Date style : yyyy-MM-dd
       Date separator : -

     Start->Settings->Control Panel->Regional Settings->Time
       Time style : HH:mm:ss
       Time separator : :
     and no AM or PM symbols (Uppercase HH - 24 Hour)

How to Convert beetween Date and Week

* How to Caculate the week number from a given date

- Algorithm

- Implemented in Lotus Notes

File: weeknumb.nsf(304 kB)

Here's the full script as it apears in the Lotus Notes FAQ
Code

REM "-------------------------------------------------------------------------------"; REM "Formulae Calculate the Week Number(01-53) for any Date. "; REM "The output follows the ISO 8601:1988 standard: ex 1997-W31-4 for 1997-07-31 "; REM "Formulae written : 1997-07-30"; REM "Formulae writer : Nikolai Sandved ([email protected]), UNI Storebrand, Norway"; REM "Formulae updated : 1997-08-04"; REM "Formulae updated by : Nikolai Sandved ([email protected]), UNI Storebrand, Norway"; REM "Version : 1.03"; REM "Tested on :Lotus Notes 4.5a, 4.6PreRelease2 and 4.6PreRelease3"; REM "This formulae is aviable in the"; REM "Lotus Notes FAQ: http://www.keysolutions.com/NotesFAQ/"; REM "More Calendar information in http://www.pip.dknet.dk/~pip10160/calendar.html"; REM "ISO 8601:1988 summary at: http://quake.stanford.edu/~lyle/ISOdate/Date.html"; REM "-------------------------------------------------------------------------------"; REM "Replace D with the date of interest."; D := TestDate; REM "***********************************"; REM " Calculate some data for this Year "; REM "***********************************"; FirstOfYear := @Date(@Year(D); 1; 1); LastOfYear := @Date(@Year(D); 12; 31); FirstDayNum := @Weekday(FirstOfYear); REM "ISO weeks start on Monday and ends on Sunday."; ISOFirstDayNum := @If(FirstDayNum = 1; 7; FirstDayNum - 1); REM " Week 1 of any year is the week that contains the first Thursday in January."; REM "=1 if 1. jan = man - thu. WeekNumber is then 1, else 0"; IsFirstWeek := 7- ISOFirstDayNum >2; REM "The first Monday after 1. jan this Year"; FirstMonday := 9 - ISOFirstDayNum; REM "Number of Days from 1. jan to D"; DaysToDateD:=(D-FirstOfYear)/60/60/24+1; REM "Number of days in Year(either 365 or 366)"; DaysInYear:=(LastOfYear-FirstOfYear)/60/60/24; REM "Number of Weeks in Year. Most years have 52 weeks, but years that start on a Thursday and leapyears that starts on a Wednesday or a Thursday have 53 weeks."; NumberOfWeeksThisYear:=@If( (ISOFirstDayNum=4 | (ISOFirstDayNum=3 & DaysInYear=366));53;52 ); REM "***********************************"; REM " Calculate some data for last Year "; REM "***********************************"; FirstOfLastYear := @Date(@Year(D)-1; 1; 1); LastOfLastYear := @Date(@Year(D)-1; 12; 31); FirstDayNumLast := @Weekday(FirstOfLastYear); REM "ISO weeks start on Monday and ends on Sunday."; ISOFirstDayNumLast := @If(FirstDayNumLast = 1; 7; FirstDayNumLast - 1); REM "Number of days in Year(either 365 or 366)"; DaysInYearLast:=(LastOfLastYear-FirstOfLastYear)/60/60/24; REM "Number of Weeks in Year. Most years have 52 weeks, but years that start on a Thursday and leapyears that starts on a Wednesday or a Thursday have 53 weeks."; NumberOfWeeksLastYear:=@If( (ISOFirstDayNumLast=4 | (ISOFirstDayNumLast =3 & DaysInYearLast=366));53;52 ); REM "****************************"; REM " Calculates the Week Number "; REM "****************************"; DDayNum := @Weekday(D); ISODDayNum := @If(DDayNum = 1; 7; DDayNum - 1); REM"Is D in the last Week of the last Year?"; DayInLastWeek := @If((DaysToDateD < FirstMonday & IsFirstWeek = 0); @Return( @Text(@Year(D)-1)+"-W"+@Text(NumberOfWeeksLastYear)+"-"+@Text(ISODDayNum)); NULL); REM "Calculate number of Complete Weeks Between D and 1.jan"; ComplNumWeeks:=@Integer((DaysToDateD-FirstMonday)/7); REM "Are there remaining days?"; RemainingDays:=@If( (DaysToDateD+1-(FirstMonday+7*ComplNumWeeks))>0); NumWeeks:= IsFirstWeek+ComplNumWeeks+1; Out := @If(RemainingDays; @If( (NumWeeks>52 & NumWeeks>NumberOfWeeksThisYear ); @Return(@Text(@Year(D)+1)+"-W01-"+ @Text(ISODDayNum)); @Return(@Text(@Year(D))+"-W"+@Right("0"+@Text(NumWeeks);2)+"-"+@Text(ISODDayNum))); @Return(@Text(@Year(D))+"-W"+@Right("0"+@Text(NumWeeks-1);2) +"-"+@Text(ISODDayNum))); Out

Code
REM "-------------------------------------------------------------------------------"; REM "Formulae Calculate the Week Number(01-53) for any Date. "; REM "The output follows the ISO 8601:1988 standard: ex 1997-W31-4 for 1997-07-31 "; REM "Formulae written : 1997-07-30"; REM "Formulae writer : Nikolai Sandved ([email protected]), UNI Storebrand, Norway"; REM "Formulae updated : 1997-08-04"; REM "Formulae updated by : Nikolai Sandved ([email protected]), UNI Storebrand, Norway"; REM "Version : 1.03"; REM "Tested on :Lotus Notes 4.5a, 4.6PreRelease2 and 4.6PreRelease3"; REM "This formulae is aviable in the"; REM "Lotus Notes FAQ: http://www.keysolutions.com/NotesFAQ/"; REM "More Calendar information in http://www.pip.dknet.dk/~pip10160/calendar.html"; REM "ISO 8601:1988 summary at: http://quake.stanford.edu/~lyle/ISOdate/Date.html"; REM "-------------------------------------------------------------------------------"; REM "Replace D with the date of interest."; D := TestDate; REM "*********************************"; REM " Calculate some data for this Year "; REM "*******************************"; FirstOfYear := @Date(@Year(D); 1; 1); LastOfYear := @Date(@Year(D); 12; 31); FirstDayNum := @Weekday(FirstOfYear); REM "ISO weeks start on Monday and ends on Sunday."; ISOFirstDayNum := @If(FirstDayNum = 1; 7; FirstDayNum - 1); REM " Week 1 of any year is the week that contains the first Thursday in January."; REM "=1 if 1. jan = man - thu. WeekNumber is then 1, else 0"; IsFirstWeek := 7- ISOFirstDayNum >2; REM "The first Monday after 1. jan this Year"; FirstMonday := 9 - ISOFirstDayNum; REM "Number of Days from 1. jan to D"; DaysToDateD:=(D-FirstOfYear)/60/60/24+1; REM "Number of days in Year(either 365 or 366)"; DaysInYear:=(LastOfYear-FirstOfYear)/60/60/24; REM "Number of Weeks in Year. Most years have 52 weeks, but years that start on a Thursday and leapyears that starts on a Wednesday or a Thursday have 53 weeks."; NumberOfWeeksThisYear:=@If( (ISOFirstDayNum=4 \| (ISOFirstDayNum=3 & DaysInYear=366));53;52 ); REM "*******************************"; REM " Calculate some data for last Year "; REM "*******************************"; FirstOfLastYear := @Date(@Year(D)-1; 1; 1); LastOfLastYear := @Date(@Year(D)-1; 12; 31); FirstDayNumLast := @Weekday(FirstOfLastYear); REM "ISO weeks start on Monday and ends on Sunday."; ISOFirstDayNumLast := @If(FirstDayNumLast = 1; 7; FirstDayNumLast - 1); REM "Number of days in Year(either 365 or 366)"; DaysInYearLast:=(LastOfLastYear-FirstOfLastYear)/60/60/24; REM "Number of Weeks in Year. Most years have 52 weeks, but years that start on a Thursday and leapyears that starts on a Wednesday or a Thursday have 53 weeks."; NumberOfWeeksLastYear:=@If( (ISOFirstDayNumLast=4 \| (ISOFirstDayNumLast =3 & DaysInYearLast=366));53;52 ); REM "************************"; REM " Calculates the Week Number "; REM "*************************"; DDayNum := @Weekday(D); ISODDayNum := @If(DDayNum = 1; 7; DDayNum - 1); REM"Is D in the last Week of the last Year?"; DayInLastWeek := @If((DaysToDateD < FirstMonday & IsFirstWeek = 0); @Return( @Text(@Year(D)-1)+"-W"+@Text(NumberOfWeeksLastYear)+"-"+@Text(ISODDayNum)); NULL); REM "Calculate number of Complete Weeks Between D and 1.jan"; ComplNumWeeks:=@Integer((DaysToDateD-FirstMonday)/7); REM "Are there remaining days?"; RemainingDays:=@If( (DaysToDateD+1-(FirstMonday+7ComplNumWeeks))>0); NumWeeks:= IsFirstWeek+ComplNumWeeks+1; Out := @If(RemainingDays; @If( (NumWeeks>52 & NumWeeks>NumberOfWeeksThisYear ); @Return(@Text(@Year(D)+1)+"-W01-"+ @Text(ISODDayNum)); @Return(@Text(@Year(D))+"-W"+@Right("0"+@Text(NumWeeks);2)+"-"+@Text(ISODDayNum))); @Return(@Text(@Year(D))+"-W"+@Right("0"+@Text(NumWeeks-1);2) +"-"+@Text(ISODDayNum))); Out

- Implemented in Microsoft Excel 2000

All the alternatives below appears in this Exceldocument:

File: ISO_8601_Date-Week-Date.xls ( 85K bytes; last changed: 2006-01-13T20:48:25 CET

Alt 1
Code

WeekToDate InputDate (B12 in example sheet above) A12: <date in Excel format> Caculations B12: =IF(OR(WEEKDAY(DATE(YEAR(A12);1;1);2)=4;AND(WEEKDAY(DATE(YEAR(A12);1;1);2)=3;(TEXT(DATE(YEAR(A12);12;31)-DATE(YEAR(A12);1;1)+1;0))=366));53;52) C12: =IF(OR(WEEKDAY(DATE(YEAR(A12)-1;1;1);2)=4;AND(WEEKDAY(DATE(YEAR(A12)-1;1;1);2)=3;(DATE(YEAR(A12)-1;12;31)-DATE(YEAR(A12)-1;1;1)+1)="366"));53;52) D12: =IF(((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))+2-((9-WEEKDAY(DATE(YEAR(A12);1;1);2))+7*(ROUND(((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))-(9-WEEKDAY(DATE(YEAR(A12);1;1);2)))/7;0))))>0;1;0) E12: =IF((7-WEEKDAY(DATE(YEAR(A12);1;1);2))>2;1;0)+(ROUND(((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))-(9-WEEKDAY(DATE(YEAR(A12);1;1);2)))/7;0))+1 Resulting Week: G12: =IF(AND((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))<((9-WEEKDAY(DATE(YEAR(A12);1;1);2))-1);IF((7-WEEKDAY(DATE(YEAR(A12);1;1);2))>2;1;0)=0);YEAR(A12)-1&"-W" & C12 & "-" & WEEKDAY(A12;2);IF(D12=1;IF(AND(E12>52;E12>B12);YEAR(A12)+1&"-W01-" & WEEKDAY(A12;2);YEAR(A12)&"-W" & RIGHT("0" & E12;2)&"-" & WEEKDAY(A12;2));YEAR(A12)&"-W" & RIGHT("0" & E12-1;2) & "-" & WEEKDAY(A12;2))) Norwegian (extremly _STUPID_ by Microsoft to translate this in the norwegian version...) B12: <date in Excel format> C12: =HVIS(ELLER(UKEDAG(DATO(�R(B12);1;1);2)=4;OG(UKEDAG(DATO(�R(B12);1;1);2)=3;(TEKST(DATO(�R(B12);12;31)-DATO(�R(B12);1;1)+1;0))="366"));53;52) D12: =HVIS(ELLER(UKEDAG(DATO(�R(B12)-1;1;1);2)=4;OG(UKEDAG(DATO(�R(B12)-1;1;1);2)=3;(DATO(�R(B12)-1;12;31)-DATO(�R(B12)-1;1;1)+1)=366));53;52) E12: =HVIS(((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))+2-((9-UKEDAG(DATO(�R(B12);1;1);2))+7*(AVRUND(((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))-(9-UKEDAG(DATO(�R(B12);1;1);2)))/7;0))))>0;1;0) F12: =HVIS((7-UKEDAG(DATO(�R(B12);1;1);2))>2;1;0)+(AVRUND(((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))-(9-UKEDAG(DATO(�R(B12);1;1);2)))/7;0))+1 G12: =HVIS(OG((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))<((9-UKEDAG(DATO(�R(B12);1;1);2))-1);HVIS((7-UKEDAG(DATO(�R(B12);1;1);2))>2;1;0)=0) ;�R(B12)-1&"-W"&D12&"-"&UKEDAG(B12;2);HVIS(E12=1;HVIS(OG(F12>52;F12>C12);�R(B12)+1&"-W01-"&UKEDAG(B12;2);�R(B1 2)&"-W"&H�YRE("0" & F12;2)&"-"&UKEDAG(B12;2));�R(B12)&"-W" & H�YRE("0" & F12-1;2) & "-" & UKEDAG(B12;2)))

Code
WeekToDate InputDate (B12 in example sheet above) A12: <date in Excel format> Caculations B12: =IF(OR(WEEKDAY(DATE(YEAR(A12);1;1);2)=4;AND(WEEKDAY(DATE(YEAR(A12);1;1);2)=3;(TEXT(DATE(YEAR(A12);12;31)-DATE(YEAR(A12);1;1)+1;0))=366));53;52) C12: =IF(OR(WEEKDAY(DATE(YEAR(A12)-1;1;1);2)=4;AND(WEEKDAY(DATE(YEAR(A12)-1;1;1);2)=3;(DATE(YEAR(A12)-1;12;31)-DATE(YEAR(A12)-1;1;1)+1)="366"));53;52) D12: =IF(((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))+2-((9-WEEKDAY(DATE(YEAR(A12);1;1);2))+7(ROUND(((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))-(9-WEEKDAY(DATE(YEAR(A12);1;1);2)))/7;0))))>0;1;0) E12: =IF((7-WEEKDAY(DATE(YEAR(A12);1;1);2))>2;1;0)+(ROUND(((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))-(9-WEEKDAY(DATE(YEAR(A12);1;1);2)))/7;0))+1 Resulting Week: G12: =IF(AND((DATE(YEAR(A12);MONTH(A12);DAY(A12))-DATE(YEAR(A12);1;1))<((9-WEEKDAY(DATE(YEAR(A12);1;1);2))-1);IF((7-WEEKDAY(DATE(YEAR(A12);1;1);2))>2;1;0)=0);YEAR(A12)-1&"-W" & C12 & "-" & WEEKDAY(A12;2);IF(D12=1;IF(AND(E12>52;E12>B12);YEAR(A12)+1&"-W01-" & WEEKDAY(A12;2);YEAR(A12)&"-W" & RIGHT("0" & E12;2)&"-" & WEEKDAY(A12;2));YEAR(A12)&"-W" & RIGHT("0" & E12-1;2) & "-" & WEEKDAY(A12;2))) Norwegian (extremly _STUPID_ by Microsoft to translate this in the norwegian version...) B12: <date in Excel format> C12: =HVIS(ELLER(UKEDAG(DATO(�R(B12);1;1);2)=4;OG(UKEDAG(DATO(�R(B12);1;1);2)=3;(TEKST(DATO(�R(B12);12;31)-DATO(�R(B12);1;1)+1;0))="366"));53;52) D12: =HVIS(ELLER(UKEDAG(DATO(�R(B12)-1;1;1);2)=4;OG(UKEDAG(DATO(�R(B12)-1;1;1);2)=3;(DATO(�R(B12)-1;12;31)-DATO(�R(B12)-1;1;1)+1)=366));53;52) E12: =HVIS(((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))+2-((9-UKEDAG(DATO(�R(B12);1;1);2))+7(AVRUND(((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))-(9-UKEDAG(DATO(�R(B12);1;1);2)))/7;0))))>0;1;0) F12: =HVIS((7-UKEDAG(DATO(�R(B12);1;1);2))>2;1;0)+(AVRUND(((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))-(9-UKEDAG(DATO(�R(B12);1;1);2)))/7;0))+1 G12: =HVIS(OG((DATO(�R(B12);M�NED(B12);DAG(B12))-DATO(�R(B12);1;1))<((9-UKEDAG(DATO(�R(B12);1;1);2))-1);HVIS((7-UKEDAG(DATO(�R(B12);1;1);2))>2;1;0)=0) ;�R(B12)-1&"-W"&D12&"-"&UKEDAG(B12;2);HVIS(E12=1;HVIS(OG(F12>52;F12>C12);�R(B12)+1&"-W01-"&UKEDAG(B12;2);�R(B1 2)&"-W"&H�YRE("0" & F12;2)&"-"&UKEDAG(B12;2));�R(B12)&"-W" & H�YRE("0" & F12-1;2) & "-" & UKEDAG(B12;2)))

Alt 2
Code

InputDate A12: <date in Excel format> Calculations H12: =RIGHT("0" & 1+INT((A12-DATE(YEAR(A12+4-WEEKDAY(A12+6));1;5) + WEEKDAY(DATE(YEAR(A12+4-WEEKDAY(A12+6));1;3)))/7);2) Resulting Week: I12: =IF(AND(MONTH(A12)=12;H12="01"); YEAR(A12)+1; IF(AND(MONTH(A12)=1;OR(H12="52";H12="53")); YEAR(A12)-1; YEAR(A12))) & "-W" & H12 & "-" & WEEKDAY(A12;2) Norwegian (extremly _STUPID_ by Microsoft to translate this in the norwegian version...) B12: <date in Excel format> H12: =H�YRE("0" & 1+HELTALL((B12-DATO(�R(B12+4-UKEDAG(B12+6));1;5) + UKEDAG(DATO(�R(B12+4-UKEDAG(B12+6));1;3)))/7);2) I12: =HVIS(OG(M�NED(B12)=12;H12="01"); �R(B12)+1; HVIS(OG(M�NED(B12)=1; ELLER(H12="52";H12="53")); �R(B12)-1; �R(B12))) & "-W" & H12 & "-" & UKEDAG(B12;2)

Code
InputDate A12: <date in Excel format> Calculations H12: =RIGHT("0" & 1+INT((A12-DATE(YEAR(A12+4-WEEKDAY(A12+6));1;5) + WEEKDAY(DATE(YEAR(A12+4-WEEKDAY(A12+6));1;3)))/7);2) Resulting Week: I12: =IF(AND(MONTH(A12)=12;H12="01"); YEAR(A12)+1; IF(AND(MONTH(A12)=1;OR(H12="52";H12="53")); YEAR(A12)-1; YEAR(A12))) & "-W" & H12 & "-" & WEEKDAY(A12;2) Norwegian (extremly _STUPID_ by Microsoft to translate this in the norwegian version...) B12: <date in Excel format> H12: =H�YRE("0" & 1+HELTALL((B12-DATO(�R(B12+4-UKEDAG(B12+6));1;5) + UKEDAG(DATO(�R(B12+4-UKEDAG(B12+6));1;3)))/7);2) I12: =HVIS(OG(M�NED(B12)=12;H12="01"); �R(B12)+1; HVIS(OG(M�NED(B12)=1; ELLER(H12="52";H12="53")); �R(B12)-1; �R(B12))) & "-W" & H12 & "-" & UKEDAG(B12;2)

The formula basis appears at the site http://www.cpearson.com/excel/weeknum.htm

Alt 3

VBA code to calculate the week from a given date
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.pvv.org/~nsaa/ISO8601.html ' http://www.pvv.org/~nsaa/excel.html ' ' 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.pvv.org/~nsaa/ISO8601.html ' http://www.pvv.org/~nsaa/excel.html ' ' 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

* How to Caculate the date from a given week

- Algorithm

- Implemented in Microsoft Excel 2000

Alt 1
Code

WeekToDateTo InputWeek A11: Caculations B11: =IF(MOD(VALUE(LEFT(A11;4));400);IF(MOD(VALUE(LEFT(A11;4));100);IF(MOD(VALUE(LEFT(A11;4));4);0;1);0);1) C11: =(VALUE(MID(A11;7;2))-1)*7+VALUE(MID(A11;10;1))-WEEKDAY(DATE(VALUE(LEFT(A11;4));1;4);2) D11: =IF(E11="june";IF(C11<(178+B11);DATE(VALUE(LEFT(A11;4));6;C11-147-B11);IF(C11<(209+B11);DATE(VALUE(LEFT(A11;4));7;C11-177-B11);IF(C11<(240+B11);DATE(VALUE(LEFT(A11;4));8;C11-208-B11);IF(C11<(270+B11);DATE(VALUE(LEFT(A11;4));9;C11-239-B11);"oct"))));E11) E11: =IF(C11<28;DATE(VALUE(LEFT(A11;4));1;C11+4);IF(C11<(56+B11);DATE(VALUE(LEFT(A11;4));2;C11-27);IF(C11<(87+B11);DATE(VALUE(LEFT(A11;4));3;C11-55-B11);IF(C11<(117+B11);DATE(VALUE(LEFT(A11;4));4;C11-86-B11);IF(C11<(148+B11);DATE(VALUE(LEFT(A11;4));5;C11-116-B 11);"june"))))) Results Date: F11: =IF(D11="oct";IF(C11<(301+B11);DATE(VALUE(LEFT(A11;4));10;C11-269-B11);IF(C11<(331+B11);DATE(VALUE(LEFT(A11;4));11;C11-300-B11);DATE(VALUE(LEFT(A11;4));12;C11-330-B11)));D11) Norwegian (extremly _STUPID_ by Microsoft to translate this in the norwegian version...) B12: <date in Excel format> C12: =HVIS(REST(VERDI(VENSTRE(B12;4));400);HVIS(REST(VERDI(VENSTRE(B12;4));100);HVIS(REST(VERDI(VENSTRE(B12;4));4);0;1);0);1) D12: =(VERDI(DELTEKST(B12;7;2))-1)*7+VERDI(DELTEKST(B12;10;1))-UKEDAG(DATO(VERDI(VENSTRE(B12;4));1;4);2) E12: =HVIS(F12="june";HVIS(D12<(178+C12);DATO(VERDI(VENSTRE(B12;4));6;D12-147-C12);HVIS(D12<(209+C12);DATO(VERDI(VENSTRE(B12;4));7;D12-177-C12);HVIS(D12<(240+C12);DATO(VERDI(VENSTRE(B12;4));8;D12-208-C12);HVIS(D12<(270+C12);DATO(VERDI(VENSTRE(B12;4));9;D12-239 -C12);"oct"))));F12) F12: =HVIS(D12<28;DATO(VERDI(VENSTRE(B12;4));1;D12+4);HVIS(D12<(56+C12);DATO(VERDI(VENSTRE(B12;4));2;D12-27);HVIS(D12<(87+C12);DATO(VERDI(VENSTRE(B12;4));3;D12-55-C12);HVIS(D12<(117+C12);DATO(VERDI(VENSTRE(B12;4));4;D12-86-C12);HVIS(D12<(148+C12);DATO(VERDI(VE NSTRE(B12;4));5;D12-116-C12);"june"))))) G12: =HVIS(E12="oct";HVIS(D12<(301+C12);DATO(VERDI(VENSTRE(B12;4));10;D12-269-C12);HVIS(D12<(331+C12);DATO(VERDI(VENSTRE(B12;4));11;D12-300-C12);DATO(VERDI(VENSTRE(B12;4));12;D12-330-C12)));E12)

Code
WeekToDateTo InputWeek A11: Caculations B11: =IF(MOD(VALUE(LEFT(A11;4));400);IF(MOD(VALUE(LEFT(A11;4));100);IF(MOD(VALUE(LEFT(A11;4));4);0;1);0);1) C11: =(VALUE(MID(A11;7;2))-1)7+VALUE(MID(A11;10;1))-WEEKDAY(DATE(VALUE(LEFT(A11;4));1;4);2) D11: =IF(E11="june";IF(C11<(178+B11);DATE(VALUE(LEFT(A11;4));6;C11-147-B11);IF(C11<(209+B11);DATE(VALUE(LEFT(A11;4));7;C11-177-B11);IF(C11<(240+B11);DATE(VALUE(LEFT(A11;4));8;C11-208-B11);IF(C11<(270+B11);DATE(VALUE(LEFT(A11;4));9;C11-239-B11);"oct"))));E11) E11: =IF(C11<28;DATE(VALUE(LEFT(A11;4));1;C11+4);IF(C11<(56+B11);DATE(VALUE(LEFT(A11;4));2;C11-27);IF(C11<(87+B11);DATE(VALUE(LEFT(A11;4));3;C11-55-B11);IF(C11<(117+B11);DATE(VALUE(LEFT(A11;4));4;C11-86-B11);IF(C11<(148+B11);DATE(VALUE(LEFT(A11;4));5;C11-116-B 11);"june"))))) Results Date: F11: =IF(D11="oct";IF(C11<(301+B11);DATE(VALUE(LEFT(A11;4));10;C11-269-B11);IF(C11<(331+B11);DATE(VALUE(LEFT(A11;4));11;C11-300-B11);DATE(VALUE(LEFT(A11;4));12;C11-330-B11)));D11) Norwegian (extremly _STUPID_ by Microsoft to translate this in the norwegian version...) B12: <date in Excel format> C12: =HVIS(REST(VERDI(VENSTRE(B12;4));400);HVIS(REST(VERDI(VENSTRE(B12;4));100);HVIS(REST(VERDI(VENSTRE(B12;4));4);0;1);0);1) D12: =(VERDI(DELTEKST(B12;7;2))-1)7+VERDI(DELTEKST(B12;10;1))-UKEDAG(DATO(VERDI(VENSTRE(B12;4));1;4);2) E12: =HVIS(F12="june";HVIS(D12<(178+C12);DATO(VERDI(VENSTRE(B12;4));6;D12-147-C12);HVIS(D12<(209+C12);DATO(VERDI(VENSTRE(B12;4));7;D12-177-C12);HVIS(D12<(240+C12);DATO(VERDI(VENSTRE(B12;4));8;D12-208-C12);HVIS(D12<(270+C12);DATO(VERDI(VENSTRE(B12;4));9;D12-239 -C12);"oct"))));F12) F12: =HVIS(D12<28;DATO(VERDI(VENSTRE(B12;4));1;D12+4);HVIS(D12<(56+C12);DATO(VERDI(VENSTRE(B12;4));2;D12-27);HVIS(D12<(87+C12);DATO(VERDI(VENSTRE(B12;4));3;D12-55-C12);HVIS(D12<(117+C12);DATO(VERDI(VENSTRE(B12;4));4;D12-86-C12);HVIS(D12<(148+C12);DATO(VERDI(VE NSTRE(B12;4));5;D12-116-C12);"june"))))) G12: =HVIS(E12="oct";HVIS(D12<(301+C12);DATO(VERDI(VENSTRE(B12;4));10;D12-269-C12);HVIS(D12<(331+C12);DATO(VERDI(VENSTRE(B12;4));11;D12-300-C12);DATO(VERDI(VENSTRE(B12;4));12;D12-330-C12)));E12)

Alt 2

VBA code to calculate the week from a given date
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.pvv.org/~nsaa/ISO8601.html ' http://www.pvv.org/~nsaa/excel.html ' ' 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 ([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 '

Eastern
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://astro.nmsu.edu/~lhuber/leaphist.html '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 ' '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 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.pvv.org/~nsaa/ISO8601.html ' http://www.pvv.org/~nsaa/excel.html ' ' 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 ([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 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://astro.nmsu.edu/~lhuber/leaphist.html '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 ' '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

A generic Calendar implemented in Excel

Download from Excel_Calendar.xls (272K bytes; last changed: 2006-01-22T00:37:38 CET)


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

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