By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,559 Online
Bytes IT Community
+ 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
  1. Function ConvertJulian(JulianDate As Long)
  2.   ConvertJulian = DateSerial(2000 + Int(JulianDate / 2000), 1, JulianDate Mod 2000) + 3651
  3. End Function
this was working, now it's not.
4 Weeks Ago #1
Share this Question
Share on Google+
8 Replies

P: 93
Here is the code I use:

Expand|Select|Wrap|Line Numbers
  1. Public Function DateToJulian(dt As Date) As String
  2.     DateToJulian = Right(Year(dt), 2) & Format(DateDiff("d", DateSerial(Year(dt), 1, 0), dt), "000")
  3. 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
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
... to convert over to the below desire result.

.... for the entire 2020 year
4 Weeks Ago #6

P: 65
You can use this expression (corrected):

Expand|Select|Wrap|Line Numbers
  1. TrueDate = DateSerial(Left(JulianDate, 5) \ 1000, 1, 
  2. Left(JulianDate, 5) Mod 1000)
or, in a query:

Expand|Select|Wrap|Line Numbers
  1. 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

Post your reply

Sign in to post your reply or Sign up for a free account.