The 1904 Time System

[embirath]

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 |
+-------------------------------------------------------------------+

 

[Ron de Bruin]

Hi Emma

Maybe you can add 1462 days in the functions

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


in the formula

 

[embirath]

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]

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 |
+-------------------------------------------------------------------+

 

[NickHK]

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]

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

 

[NickHK]

Yes, pass strings rather than dates, as you cannot be sure if the Excel date
(a double) is based on 1904 or not.
In your UDF, you can safely use VBA's Date data type (as long as it is
within its limits) to perform your calculation.
How do you get "January" from your astronomical date/time ?

As for books, a good place to start is your local library. Failing that you
can request many others from different libraries.

NickHK

 

[embirath]

To get the month from the string I do the following. I first extract th
year from the string, and then I create a date type of the year shift
So if the year is 2006, I create the date "Dec 31, 2006 00:00:00". The
I use "DateAdd" to add the Day Of Year to that date. Then, I use DataAd
again to use add the time, like 13:30:40, leaving me with a date type.

I then pass this date type back to the spreadsheet. But what I shoul
do instead then is pass the string back to the spreadhseet, and hav
the spreadsheet do the formatting.

Thanks for the advice! It makes sense.
Thanks
Emm

 

[embirath]

Maybe I didn't answer your question... the "021" in the string is the
Day of Year, and so the 21st day of the year means we're in January.
Maybe you figured that out from my last email, but I think I
misunderstood your question originally.

Emma

 

[NickHK]

Emma,
If you pass a String back, it will need to be already formatted (in VBA) in
the way you want. Excel will not change it when 1904 system is changed (as
it's a String, not a Date), but it makes calculations more difficult.
If you pass a Date back to the worksheet, you can format it in Excel to look
how you want and you can easily make calculations with it. However the
displayed date will change if you are using 1904 system or not.
The 1904 setting is a workbook setting. When it is changed, a recalculation
is triggered. You can use that in your functions to adjust accordingly and
display the correct date.
These seem to work:

Const Factor1904To1900 As Long = 1462

Public Function AstroDateToDate(ByVal AstroDate As String) As Date
Dim TempDate As Date

TempDate = DateSerial(Left(AstroDate, 4), 1, 0) + CLng(Mid(AstroDate, 6, 3))
TempDate = TempDate + TimeValue(Right(AstroDate, 8))
If ThisWorkbook.Date1904 = True Then TempDate = TempDate - Factor1904To1900
AstroDateToDate = TempDate
End Function

Public Function DateToAstroDate(ByVal InDate As Date) As String

If ThisWorkbook.Date1904 = True Then InDate = InDate - Factor1904To1900
DateToAstroDate = Year(InDate) & "-" & Format(DatePart("y", InDate), "000")
& "T" & Format(TimeValue(InDate), "hh:mm:ss")
End Function

If you need to handle situations where the 1904 setting may be chnaged
through code, so the ActiveWorkbook is not the workbook being changed (as it
is when Tools>Option>calculation is changed) you would need to check the
something like the Caller.Parent.Parent.Date1904 = True instead. I'll leave
that up to you.

NickHk

 

[NickHK]

Emma,
Out of interest, what does the "T" indicate ?

NickHK

 

[embirath]

Hi Nick

Wow, those functions were so much shorter and simpler than mine! Thanks
for sending them to me.

That is a good question, about the "T".. I've never thought about what
it stands for. It just separates the date and time, so maybe it just
means "time"? Whatever it is, it stays constant, and is not part of the
actual date. It is just a separator. Sometimes you see the dates written
as 2006-071/00:10:30, with a slash instead.

Thanks again for all your help! This forum is great.

Emma