date formating help

[Pal]

I have the function below that returns the YEAR, MONTH, DAY, and HOUR from a
given Julian Day number as input.
But I want to use this as a function that returns MM/DD/YYYY HH:MM in one
cell.

For instance:
tjd= 2451544.5
result = 01/01/2000 12:00
Thanks
Pal


Sub reversejuliandate()
Dim tjd As Double
Dim jyear, jmonth, jday As Long
Dim jhour As Double
Dim coord As Long

tjd = Sheet6.Cells(3, 5)
coord = swe_revjul_d(tjd, 1, jyear, jmonth, jday, jhour)
Sheet6.Cells(20, 2) = jyear
Sheet6.Cells(21, 2) = jmonth
Sheet6.Cells(22, 2) = jday
Sheet6.Cells(23, 2) = jhour
End Sub

 

[JE McGimpsey]

First, note that jd 2451544.5 is 01/01/2000 00:00, not 12:00 (assuming
GMT. If you're adjusting to a local time zone, you should adjust the
macro below).

One way:

Public Function reversejuliandate(ByVal tjd As Double) As Double
Const base1900 As Double = 2415018.5
Dim adj1904 As Double
If TypeName(Application.Caller) = "Range" Then _
adj1904 = Application.Caller.Parent.Parent.Date1904 * 1462
reversejuliandate = tjd - base1900 + adj1904
End Function

The "if Typename(...)..." line adjusts for the calling workbook's date
system if the function is called from a cell. For instance:

B6: =reversejuliandate(E3)

 

[Pal]

Thanks but I am not sure what you are doing here.

I thought I needed to convert my Jyear, Jmonth, Jday, Jhour into a
MM/DD/YYYY HH:MM format.
I tried adding them directly and displaying them in that format but it did
not work.

 

[JE McGimpsey]

You could use your intermediate variables:

dDateTime = DateSerial(jyear, jmonth, jday) + _
TimeSerial(jhour, 0, 0)

But that seems to be the long way round when you only have to do a
subtraction. Both julian and XL dates are integer offsets from a base
date with time being represented as a fractional day. You need only
subtract the difference in base dates from the jd to get the XL date.

Why bother with additional variables and function?