![]() |
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)! |
---|---|
|
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 (nsaa@ProbabilityOf.com)
First you should get familiar with the ISO 8601 standard.
* 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
"%Y-%m-%dT%H:%M:%S" |
date "+%Y-%m-%dT%H:%M:%S" |
* CGI - Not yet -
(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))))))) |
import java.text.SimpleDateFormat; import java.util.Date; |
Date now = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); sdf.format( now ); |
use POSIX qw(strftime); my $datetime = strftime("%Y-%m-%dT%H:%M:%S", localtime()); |
<?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>"; ?> |
Date : 1999-01-03 Week : 1998-W53 Full week : 1998-W53-7 |
// 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); |
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 |
ISO Date & Time ISO Date & Time Ext ISO Week ISO Week Ext --------------- ------------------- -------- ------------ 20030129T161157 2003-01-29T16:11:57 2004W011 2004-W01-1 |
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 |
YYYYMMDD YYYY-MM-DDThh:mm:ss 20000907 2000-09-07T21:56:54 |
--- 102 => yyyy.mm.dd stuff(stuff(convert(varchar, datefield, 102), 5, 1, '-'), 8, 1, '-') + 'T' + convert(char(8), datefield, 108), |
Also see my Excel page for implementations in Excel (xls).
'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
' 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 |
Thanks to Michael.Rosqvist AT lansforsakringar.se
SELECT CONVERT(CHAR(23), current_timestamp, 126) |
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
- Algorithm
File: weeknumb.nsf(304 kB)
Here's the full script as it apears in the Lotus Notes FAQ
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 (nsaa@pvv.org), UNI Storebrand, Norway"; REM "Formulae updated : 1997-08-04"; REM "Formulae updated by : Nikolai Sandved (nsaa@pvv.org), 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 |
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
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
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
Public Function DateToWeek(ByVal datDate As Date, _ Optional ByVal bytTruncFormat As Byte = 0, _ Optional ByVal bytShortLongFormat As Byte = 0) As String '****************************************************************************** ' ' Macro created 2003-03-18 by Nikolai Sandved (nsaa@pvv.org) ' ' Description: This Function return the ISO8601 week ' This code appears on the site ' http://www.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
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
Public Function WeekToDate(ByVal strWeek As String) As Date '****************************************************************************** ' ' Macro created 2001-01-18 by Nikolai Sandved (nsaa@pvv.org ' ' Description: This Function return the date ' This code appears on the site ' http://www.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 (nsaa@pvv.org ' See Leap Year rule at ' http://www.computerbooksonline.com/program/1032chap.htm#E29E11 ' http://en.wikipedia.org/wiki/Leap_year ' http://aa.usno.navy.mil/faq/docs/leap_years.html ' http://www.timeanddate.com/date/leapyear.html '****************************************************************************** bIsLeapYear = ((intYear Mod 4 = 0) _ And (intYear Mod 100 <> 0) _ Or (intYear Mod 400 = 0)) End Function ' |
Public Function EasterDateCalendarFAQ(ByVal strYear As String) As Date '****************************************************************************** ' Macro created 2003-03-24 by Nikolai Sandved (nsaa@pvv.org) ' Returns the Easter Day for a given year ' ' Input: ' strYear - Year (It should be correct Beetween 532 and year 4000 approx) ' ' Output: ' EasterDateCalendarFAQ - Returns the Easter Day for the given year strYear ' 'Based on the Calendar FAQ formula 'http://www.tondering.dk/claus/cal/node3.html#SECTION003120000000000000000 ' 'The formulas presented here gives the same ansvers: 'http://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 |
![]() |
![]() |
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)! |