443,730 Members | 1,559 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

# How to convert Juliandate to regular date for 2020

 P: 4 I've been making several changes to get the formula to convert over to regular dates for 2020 in date order for reports, Expand|Select|Wrap|Line Numbers 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. 4 Weeks Ago #1
8 Replies

 P: 93 Here is the code I use: Expand|Select|Wrap|Line Numbers 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   4 Weeks Ago #2

 P: 65 That's a weird function. Could you provide some sample values for your JulianDate and what dates you expect these to be converted to? 4 Weeks Ago #3

 P: 4 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. 4 Weeks Ago #4

 P: 65 OK. Could you please provide some sample values for your JulianDate and what dates you expect these to be converted to? 4 Weeks Ago #5

 P: 4 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 4 Weeks Ago #6

 P: 65 You can use this expression (corrected): Expand|Select|Wrap|Line Numbers TrueDate = DateSerial(Left(JulianDate, 5) \ 1000, 1,  Left(JulianDate, 5) Mod 1000) or, in a query: Expand|Select|Wrap|Line Numbers TrueDate: DateSerial(Left([JulianDate], 5)\1000,1,Left([JulianDate],5) Mod 1000) What does the /0011 part mean? 4 Weeks Ago #7

 P: 4 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. 3 Weeks Ago #8

 P: 65 OK. Then please mark as answered. 3 Weeks Ago #9