How to Convert Julian Date to Regular Date for All of 2020

[rmhenry67]

I've been making several changes to get the formula to convert over to regular dates for 2020 in date order for reports,

Function ConvertJulian(JulianDate As Long)
  ConvertJulian = DateSerial(2000 + Int(JulianDate / 2000), 1, JulianDate Mod 2000) + 3651
End Function

this was working, now it's not.

[Nauticalgent]

Here is the code I use:

Public Function DateToJulian(dt As Date) As String
    DateToJulian = Right(Year(dt), 2) & Format(DateDiff("d", DateSerial(Year(dt), 1, 0), dt), "000")
End Function
 

[cactusdata]

That's a weird function.
Could you provide some sample values for your JulianDate and what dates you expect these to be converted to?

[rmhenry67]

I run jobs on mainframe that works with julian dates. I then ftp my reports down to a txt file. I've created Macros for each applications that have their own schedules. I need the julian dates to convert overt to regular date mm/dd/yy. I run a job that has the 365 or as 2020 has 366 days as julian dates to make sure this formula will work.

[cactusdata]

OK.
Could you please provide some sample values for your JulianDate and what dates you expect these to be converted to?

[rmhenry67]

Trying to convert all 2020 dates from julian date/start time
20001/0011
20002/0011
20003/0011
20004/0011
... to convert over to the below desire result.

01/01/20
01/02/20
01/03/20
01/04/20
.... for the entire 2020 year

[cactusdata]

You can use this expression (corrected):

TrueDate = DateSerial(Left(JulianDate, 5) \ 1000, 1, 
Left(JulianDate, 5) Mod 1000)

or, in a query:

TrueDate: DateSerial(Left([JulianDate], 5)\1000,1,Left([JulianDate],5) Mod 1000)

What does the /0011 part mean?

[rmhenry67]

the /0011 is the start time of my test data so the jobs will fall out on the date and time they are expected to run.

[cactusdata]

OK. Then please mark as answered.