[vsni.co.uk] Contact us | Site map
Forum | VSN International Forum Index
Author Message
Post new topic

<  GenStat  ~  GenStat - Dates

Jeff Wood
Posted: Thu Mar 22, 2012 11:31 pm Reply with quote
Joined: 19 Apr 2010 Posts: 62
Dear GenStatters

Yet again I have trouble with dates. I am reading dates from Excel and
thought that the simplest thing to do would be to add on the result of
date(31;12;1899). In practice I seem to have to add on the result of
date(30;12;1899).

Where has the extra day come from?

Jeff
--

Jeff Wood, Ph.D. A.Stat.
Fenner School of Environment and Society/Statistical Consulting Unit
WK Hancock Building 43
Australian National University
CANBERRA ACT 0200
Australia

Phone +61 2 6125 4741
Fax +61 2 6125 0757

ANU CRICOS PROVIDER NO. 00120C

Post generated using Mail2Forum (http://www.mail2forum.com)
View user's profile Send private message
david@vsn.co.nz
Posted: Thu Mar 22, 2012 11:54 pm Reply with quote
Joined: 30 Jul 2009 Posts: 186
Dear Jeff,

Quote:
Yet again I have trouble with dates. I am reading dates from Excel and
thought
Quote:
that the simplest thing to do would be to add on the result of
date(31;12;1899).
Quote:
In practice I seem to have to add on the result of date(30;12;1899).
Where has the extra day come from?

This is because Excel has the non-existent day 29/2/1900 it's its date
system.
Microsoft programmers originally mucked-up dates as they didn't recognise
that
only centuries divisible by 400 get to be leap years, so that 1900 wasn't a
leap year
but 2000 was. Because the Excel format is a set standard, they can't fix
this,
although they introduced a base date of 1904 as an option is to the Excel
file
format (in which case you would need to add DATE(31;12;1903) to these
dates).
This just avoids the problem rather than fixing it.

The rule is "Years that are evenly divisible by 100 are not leap years,
unless they
are also evenly divisible by 400, in which case they are leap years."
If you go through the spreadsheet date conversion to GenStat dates, then
the missing 29/2/1900 is eliminated correctly. To get this working
automatically,
just put a :D (for Date format) at the end of the Excel column name for
dates via IMPORT or File | Open. I convert 1904 based dates also to GenStat
dates
when these are present (but if you go via the clipboard, Excel doesn't
include
the information that they are using 1904 based dates in the clipboard data,
in which
case you suddenly lose 4 years from your dates when pasting into GenStat
and even an Excel sheet with 1900 based dates ARRRH!).

Regards, David.
______________________________________________
Dr David Baird      Statistical Consultant and GenStat Developer
VSN (NZ) Limited (David@VSN.CO.NZ)
8 Mariposa Crescent, Aidanfield, Christchurch 8025, New Zealand
Ph +64 3 3350588 Cell +64 21 1160803

Post generated using Mail2Forum (http://www.mail2forum.com)
View user's profile Send private message
Jeff Wood
Posted: Fri Mar 23, 2012 1:33 am Reply with quote
Joined: 19 Apr 2010 Posts: 62
Dear GenStatters

Many thanks to David Baird for his detailed explanation of how dates are
handled in Excel.
Is it the same story for Access?

I nearly always use IMPORT to read from Excel spreadsheets, and this
seems to have a bit less flexibility than other ways of getting data in.
I also do not like to modify the column headings in spreadsheets.
This is because I want to be able to read updated versions of datasets
after corrections have been made without having to go through them and
repeat a lot of edits.

Jeff
--

Jeff Wood, Ph.D. A.Stat.
Fenner School of Environment and Society/Statistical Consulting Unit
WK Hancock Building 43
Australian National University
CANBERRA ACT 0200
Australia

Phone +61 2 6125 4741
Fax +61 2 6125 0757

ANU CRICOS PROVIDER NO. 00120C

Post generated using Mail2Forum (http://www.mail2forum.com)
View user's profile Send private message
david@vsn.co.nz
Posted: Fri Mar 23, 2012 3:07 am Reply with quote
Joined: 30 Jul 2009 Posts: 186
Hi Jeff,

Quote:
Is it the same story for Access?

To read Access into GenStat you have to use ODBC which provides a
consistent date format across all packages, and hence the internal format
of the dates in Access is irrelevant.

Regards, David.
______________________________________________
Dr David Baird      Statistical Consultant and GenStat Developer
VSN (NZ) Limited (David@VSN.CO.NZ)
8 Mariposa Crescent, Aidanfield, Christchurch 8025, New Zealand
Ph +64 3 3350588 Cell +64 21 1160803

Post generated using Mail2Forum (http://www.mail2forum.com)
View user's profile Send private message

Display posts from previous:  

All times are GMT
Page 1 of 1
Post new topic

Jump to:  

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum