| Author |
Message |
< GenStat ~ GenStat - Dates
|
| Jeff Wood |
 Posted: Thu Mar 22, 2012 11:31 pm |
 |
|
|
Joined: 19 Apr 2010
Posts: 41
|
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) |
|
|
| Back to top |
 
|
| david@vsn.co.nz |
| Posted: Thu Mar 22, 2012 11:54 pm |
|
|
|
Joined: 30 Jul 2009
Posts: 100
|
Dear Jeff,
Quote: Yet again I have trouble with dates. I am reading dates from Excel and
Quote: that the simplest thing to do would be to add on the result of
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) |
|
|
| Back to top |
|
| Jeff Wood |
| Posted: Fri Mar 23, 2012 1:33 am |
|
|
|
Joined: 19 Apr 2010
Posts: 41
|
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) |
|
|
| Back to top |
|
| david@vsn.co.nz |
| Posted: Fri Mar 23, 2012 3:07 am |
|
|
|
Joined: 30 Jul 2009
Posts: 100
|
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) |
|
|
| Back to top |
|
|
|
![[vsni.co.uk]](/common/images/address.gif){kind=small}
Contact us | Site map
News Feed
