Hi everyone

I wrote a couple functions, which allow me to convert time strings as
used in astronomy ("2006-230T14:55:00") to a regular Date Type, and
vice versa. As I finished these functions, I started reading about
negative time, and how Excel can not handle negative time, unless you
use the 1904 time system. So, I switched to the 1904 time system, but
now all the functions I wrote create dates that are 4 years and 1 day
off... :-(

How can I update the functions, so that they now work in the 1904 time
system?

I attached the module with the functions, in case you need that to
answer my questions.
Thanks!
Emma


+-------------------------------------------------------------------+
|Filename: Module1.bas.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5181 |
+-------------------------------------------------------------------+

--
embirath
------------------------------------------------------------------------
embirath's Profile: http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=570926

Hi Emma

Maybe you can add 1462 days in the functions

--
Regards Ron de Bruin
http://www.rondebruin.nl


in the formula
"embirath" wrote in message
...

Hi everyone

I wrote a couple functions, which allow me to convert time strings as
used in astronomy ("2006-230T14:55:00") to a regular Date Type, and
vice versa. As I finished these functions, I started reading about
negative time, and how Excel can not handle negative time, unless you
use the 1904 time system. So, I switched to the 1904 time system, but
now all the functions I wrote create dates that are 4 years and 1 day
off... :-(

How can I update the functions, so that they now work in the 1904 time
system?

I attached the module with the functions, in case you need that to
answer my questions.
Thanks!
Emma


+-------------------------------------------------------------------+
|Filename: Module1.bas.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5181 |
+-------------------------------------------------------------------+

--
embirath
------------------------------------------------------------------------
embirath's Profile: http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=570926

Hi Ron

Thanks for your quick response. I thought maybe there was a way to tell
VBA to use the 1904 time system, but now I read somewhere that it only
uses the 1900 time system. So yes, I guess I have to add the time
difference manually.

I added the following, so that I can use it from workbooks of both
systems:

If (ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If

Thanks!
Emma


--
embirath
------------------------------------------------------------------------
embirath's Profile: http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=570926

Well, I made some changes, and got it to work. But I'm a bit confused
about how this works still.

It looks like when I pass a Date from a worksheet to the VBA function,
I don't need to do the conversion (ie the addition of 1462 days). But,
if I create a date inside the VBA function, and then pass it to the
worksheet, I DO see the 4yr-1day discrepancy, and I do need to subtract
the 1462 days.

Do you understand why it works one way but not the other? How do I know
when I need to do a conversion, and when not (except for just checking
the answers to see what works..?)

I have uploaded a couple of simplified functions that just illustrate
what I'm confused about, without all the other stuff. The first
function needs no conversion, the second one does.

Thanks again for your input.
Emma


+-------------------------------------------------------------------+
|Filename: Module2.bas.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5182 |
+-------------------------------------------------------------------+

--
embirath
------------------------------------------------------------------------
embirath's Profile: http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=570926

To avoid confusing Excel, VBA and yourself, only use string date as inputs
(with a 3 or 4 digit year, not 2) and use VBA's date function and Date data
type.
<From Help Date Data Type
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999....etc
</From Help

Depending how far back you are going, there that whole business of change in
calenders, missed/extra leap year etc, that it seems anything more than
about 150 years ago is somewhat guesswork.

NickHK

"embirath" wrote in
message ...

Well, I made some changes, and got it to work. But I'm a bit confused
about how this works still.

It looks like when I pass a Date from a worksheet to the VBA function,
I don't need to do the conversion (ie the addition of 1462 days). But,
if I create a date inside the VBA function, and then pass it to the
worksheet, I DO see the 4yr-1day discrepancy, and I do need to subtract
the 1462 days.

Do you understand why it works one way but not the other? How do I know
when I need to do a conversion, and when not (except for just checking
the answers to see what works..?)

I have uploaded a couple of simplified functions that just illustrate
what I'm confused about, without all the other stuff. The first
function needs no conversion, the second one does.

Thanks again for your input.
Emma


+-------------------------------------------------------------------+
|Filename: Module2.bas.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5182 |
+-------------------------------------------------------------------+

--
embirath
------------------------------------------------------------------------
embirath's Profile:
http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=570926

Let me see if I'm following you... Are you suggesting that I call the
function with a string as input parameter (like "2006-021T13:10:00"),
and have the function return a string as well (like "Jan 21, 2006
13:10:00"), instead of a date type?

Then I suppose the Excel worksheet can convert this string to a date?

If I do that, then what do you mean when I say I should use the "VBA
date function"?

Thanks so much for your help.

Btw, do you have an Excel book that you recommend? I purchased the
"Excel 2003 Programming Inside Out", but it doesn't seem to be able to
answer a lot of my questions.

Emma


--
embirath
------------------------------------------------------------------------
embirath's Profile: http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=570926