converting a date back to a raw number

Thread: converting a date back to a raw number

1. mj
Senior Member
Join Date
Dec 1969
Posts
848

converting a date back to a raw number

Does anyone know how to take a date such as the following, 2/24/2001 and convert it into a raw number, the way Microsoft does. For example, if you have a date field in access or excel, and enter the the number 36945 into the field, when you move off of it, it automatically converts it into the date 2/24/2001. If anyone has any guidance as to how MS Calculates this, could you let me know. <BR><BR>Thanks,<BR>mj

2. Senior Member
Join Date
Dec 1969
Posts
1,362

RE: converting a date back to a raw number

Well mj<BR><BR>How does<BR>36945 <BR>convert to this?<BR>2/24/2001. <BR><BR>I would guess that you capture the onBlur event<BR>then run through the numbers and determine what kind of date it is www.irt.org has some code on that.

3. mj
Senior Member
Join Date
Dec 1969
Posts
848

poorly worded

I figured out how to do it by reading up on the date diff function. That got my function working properly<BR><BR>The app I am working on uses a date numbering format that is way weird, for example 03/08/2001 is stored in this system as 2451977<BR><BR>I had to figure out how to find the numerical equivilant of 03/08/2001 in microsoft world, The datediff function did that for me, then I had to add the numerical value that is the difference between the other systems date and the result of the datediff function.<BR><BR>Thanks for looking though.<BR><BR>mj

4. mj
Senior Member
Join Date
Dec 1969
Posts
848

RE: converting a date back to a raw number

Sorry I described it wrong. Here is what it should have said though: If you put a whole number into a cell in Access or Excel that is a numeric field, say 36945, then reformat that cell so that it is a date field, it converts the number to 02/23/01. How does the conversion work?<BR><BR>However, since I did what I should have done in the first place, and did some R&D, I found out that MS calculates this as the number of days since 1/1/1900. <BR><BR>I tried this, and found out that if I do a DateDiff("d", 1/1/1900, now) it will return the number of days between now and 1/1/1900.<BR><BR>I then incorporated this into my function and voile, it works<BR><BR>Thanks Viv,<BR>mj

5. Senior Member
Join Date
Dec 1969
Posts
96,118

You are going to KICK yourself...

You see, a DATE/TIME value in VBScript is actually a VARIANT in OLE terms. And that variant just happens to *actually* be a DOUBLE (that is, an 8-byte floating point number). And, so, you can convert back and forth between the numeric form and the date/time form via (ready for this?) ...<BR><BR>&#060;%<BR>numberForm = CDbl( dateTimeValue ) <BR>dateForm = CDate( numberForm )<BR>%&#062;<BR><BR>Why a double instead of an integer? When you note that the number is the number of days since 1/1/1900? Because the *fractional* part of the double is the time of day within that day!<BR><BR>And here is a fun fact: There is NO DIFFERENCE between "2/24/2001" and "2/24/2001 0:00:00 AM" as the date/time value is represented. (That is, both have zero to the right of the decimal point!) So, as a consequence, VBScript messes up the display of "2/24/2001 0:00:00 AM" unless you call FormatDateTime to ask for it to be explicitly shown with both date and time.<BR><BR>

6. mj
Senior Member
Join Date
Dec 1969
Posts
848

RE: You are going to KICK yourself...

Thanks Bill

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•