Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert to/from Excel date
Is there any code or an article that lists exactly how to convert to/from a
datetime as stored in an Excel XLSX file and year/minth/day/hour/minute/second? Also, from playing with it, it appears that Excel thinks 2/29/1900 existed (it was not a leap yer). -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert to/from Excel date
Not to sure what you mean when you say convert to from date. Convert to what?
You can use the text function to get a date converted to text. =Text(A1, "yyyy/mm/dd/hh/mm/ss") As for the leap year issue that is not a bug in XL. It is a bug in Lotus 123. For XL to be compatable it had to make the same mistake as Lotus. -- HTH... Jim Thomlinson "David Thielen" wrote: Is there any code or an article that lists exactly how to convert to/from a datetime as stored in an Excel XLSX file and year/minth/day/hour/minute/second? Also, from playing with it, it appears that Excel thinks 2/29/1900 existed (it was not a leap yer). -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert to/from Excel date
Now that's what I call being serious about compatibility :)
I have some C# code that parses an XLSX file. So it reads that the cell is formatted as a date_time and has a value of 12345.678 - I need to convert that to year, month, day, hour, second, minute to create a DateTime object. And then I have to do the reverse when creating an XLSX output file. One thing I have learned about dates - it is very very difficult to get it right (Lotus 123 being a good example). So if there is tested code that does this somewhere, it would be very helpful. Especially if there are other purposful errors in addition to the 2/29/1900 one. -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm "Jim Thomlinson" wrote: Not to sure what you mean when you say convert to from date. Convert to what? You can use the text function to get a date converted to text. =Text(A1, "yyyy/mm/dd/hh/mm/ss") As for the leap year issue that is not a bug in XL. It is a bug in Lotus 123. For XL to be compatable it had to make the same mistake as Lotus. -- HTH... Jim Thomlinson "David Thielen" wrote: Is there any code or an article that lists exactly how to convert to/from a datetime as stored in an Excel XLSX file and year/minth/day/hour/minute/second? Also, from playing with it, it appears that Excel thinks 2/29/1900 existed (it was not a leap yer). -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert to/from Excel date
On Thu, 3 Apr 2008 16:54:00 -0700, David Thielen wrote:
Now that's what I call being serious about compatibility :) I have some C# code that parses an XLSX file. So it reads that the cell is formatted as a date_time and has a value of 12345.678 - I need to convert that to year, month, day, hour, second, minute to create a DateTime object. And then I have to do the reverse when creating an XLSX output file. One thing I have learned about dates - it is very very difficult to get it right (Lotus 123 being a good example). So if there is tested code that does this somewhere, it would be very helpful. Especially if there are other purposful errors in addition to the 2/29/1900 one. I don't know about "tested code", but the conversion should be pretty straightforward, including adding in a day for February 29, 1900. (I have read that this mistake started with Lotus 1-2-3, which predated Excel, and that MS incorporated this error into Excel for "compatibility". Excel stores dates/times as days and fractions of a day. If the worksheet was using the 1900 date system, then 1 = 1 Jan 1900. And 12 noon on that day would be represented as 1.5. If using the 1904 date system, then 1 = 2 Jan 1904 (0 = 1 Jan 1904). (Date1904 is a Workbook property, but I don't know how that would be stored in the XLSX file). --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert to/from Excel date
On Thu, 3 Apr 2008 16:54:00 -0700, David Thielen wrote:
Now that's what I call being serious about compatibility :) I have some C# code that parses an XLSX file. So it reads that the cell is formatted as a date_time and has a value of 12345.678 - I need to convert that to year, month, day, hour, second, minute to create a DateTime object. And then I have to do the reverse when creating an XLSX output file. One thing I have learned about dates - it is very very difficult to get it right (Lotus 123 being a good example). So if there is tested code that does this somewhere, it would be very helpful. Especially if there are other purposful errors in addition to the 2/29/1900 one. I forgot to mention that, within VBA, CDate will convert your value to a date, but the VBA date system does not include Feb 29, 1900 In the Immediate Window: ?cdate(12345.678) 10/18/1933 4:16:19 PM Since the VBA date system does not include Feb 29, 1900, it starts off with 1 = 12/31/1899 So there is a one day difference until Mar 1, 1900 compared with Excel; then they agree. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to convert solar date to lunar date using excel? | Excel Discussion (Misc queries) | |||
How to convert Gregorian date into Hijri Date in Excel 2007? | Excel Discussion (Misc queries) | |||
VBA convert day and date from text string to Excel date | Excel Programming | |||
Convert an Excel date to a date of my format in VBA | Excel Programming | |||
how do I convert a UK date to a US date in excel? | Excel Discussion (Misc queries) |