us fr nl

VBA: Date and time

Using dates older then 1-1-1900 in worksheets
Excel is limited to handling dates >= 1-1-1900 in worksheets. See what happens if you enter 1-1-1899 in a cell and format the cell as a Date. In VBA this is different because then it can handle dates up to 100 AD. If you want to work with dates, for example add 124 days to 1 January 1605, then you can wite a User Defined Function to do this for you. This article demonstrates this.

Convert year, week and day number to a date according to the ISO 8601 standard
If you ever had to deal with weeknumbers you know that there's no such thing as a common standard. Not until until the last couple of years anyway. Microsoft sort of determined their own standard for weeknumbers in the past. Those of you that had to work with Schedule+ probably know what I mean. But luckily for us ISO has defined a standard that is being used more and more. With a simple VBA routine we can use that standard in Office applications as well. If you do a little digging you can probably find some code that will give you an ISO weeknumber for a given data. This function does the reverse; for any given year, week and day number it will return the date according to ISO standards. If you understand this routine you pretty much understand the ISO 8601 definition of dates.

Atom Feed
Contact | About This Application | Scriptorium Website