|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
September 14th, 2007, 06:14 AM
|
|||
|
|||
|
Help reversing a date conversion (maths problem)
Hi
I have a script that converts an excel form to a cvs document. However, there is a problem when converting dates. If a field is formatted as a date (dd/mm/yyyy) using a cell format, then when it gets converted, the date value is converted to a 5 digit number and I can't figure out what the formula is for getting from a standard dd/mm/yyyy date to a 5 digit number, or how to reverse is. Here are some examples: 03/07/2003 converts to 37805 10/09/2007 converts to 39335 03/08/2007 converts to 39297 This is the script that does the conversion (its PHP): http://scripts.ringsworld.com/database-tools/xls2mysql/inc/Excel/reader.php.html If anyone can explain the maths behind the conversion, but more importantly how to reverse it. Then that would be great! Thanks! Last edited by adam678 : September 14th, 2007 at 07:49 AM.
|
|
#2
September 14th, 2007, 12:06 PM
|
||||
|
||||
|
In Excel, the five digit number is the number of days since 1/1/1900. Today is 39339 (in Excel).
Your numbers appear to be different (maybe they used 1901, but in any case it's number of days). Using your examples as a benchmark you could set a variable to one of these "known" values. Then calculate the difference between your number and this variable. This will tell you the difference in the number of days. Now convert to datetime and see if you are right. This should verify that these do represent days since some time in the past. Doing a simple conversion to a datetime will "reverse" this. Since a date can be represented as a decimal, you can convert it to a datetime and get a valid date.
__________________
Slarentice (origin:Shadow Wizard of ASP Free) [noun] A slave and apprentice of the Wizard's Circle (specifically of mehere) at ASP Free. ---- If shemzilla takes over, it's best to be on his good side  
|
|
#3
September 15th, 2007, 10:10 AM
|
|||
|
|||
|
Thanks Lauramc!
I can convert the numbers back to dates using the 'DateAdd' function, i.e. Code:
Response.write(DateAdd("d", 37805-2, "01/01/1900"))
The date is two days out for some reason, might be something to do with leap years or something.... But yeah, thanks for your help with this, its much appreciated!
|
|
#4
September 15th, 2007, 10:54 AM
|
||||
|
||||
|
Quote:
You can also convert the numbers back to dates using formatdatetime() Code:
Response.write formatdatetime(37805,vbShortDate) & "<BR>" Response.write formatdatetime(39335,vbShortDate) & "<BR>" Response.write formatdatetime(39297,vbShortDate) & "<BR>"
__________________
If I've helped you then add to my rep please... Click on the  !Got Milk?? 
|
|
| Viewing: ASP Free Forums > Other > ASP Free Lounge > Help reversing a date conversion (maths problem) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
