topic Re: 7-digit Julian Date convert to MM/DD/YYYY in App Development

7-digit Julian Date convert to MM/DD/YYYY

AlanR — Tue, 11 Jul 2023 23:29:25 GMT

I have seen several post about this one. Looking for the correct formula to convert a 7-digit julian date (e.g. 2023015) form a field to convert into a MM/DD/YYYY Date format. The first 4 characters of the Julian date (2023) represents the year and the last 3 characters represents the day of the year (15th day of January) or 01/15/2023. I am trying to convert a field with Julian Dates to convert to the actually calendar date in Data Manager using Calculated Field.

Any advice?

Re: 7-digit Julian Date convert to MM/DD/YYYY

md_talib — Wed, 12 Jul 2023 06:11:58 GMT

Use this

=Date(Date#(2023015,'YYYYMMDD'),'DD/MM/YYYY')

Re: 7-digit Julian Date convert to MM/DD/YYYY

AlanR — Wed, 12 Jul 2023 12:08:28 GMT

Didn't quite work. Somehow I need to covert the last 3 characters (represents the day of the year) of the yyyyDDD Julian Date to Month and Day (MM/DD/YYYY)

Examples:

2023015 is 1/15/2023

2023260 is 9/16/2023

2023365 is 12/31/2023

Re: 7-digit Julian Date convert to MM/DD/YYYY

AlanR — Wed, 12 Jul 2023 12:52:01 GMT

Here is the last 3 character conversion from the Julian date I am trying to explain.

Julian date: 2023260

Positions 1-4 is the year: 2023

Positions 5-7 is the day: 260 (260 day = September 17th)

Don't forget about leap years. I will be using this formula often.

Re: 7-digit Julian Date convert to MM/DD/YYYY

Or — Wed, 12 Jul 2023 13:00:55 GMT

Perhaps:

MakeDate(Left(YourDate,4)) + right(YourDate,3) - 1

Use the year part to establish the starting point, and then just add the number of days (subtracting one because the year starts on day 1).

Re: 7-digit Julian Date convert to MM/DD/YYYY

AlanR — Wed, 12 Jul 2023 13:19:45 GMT

All,

Date(MakeDate(Floor(DateField / 1000) ) + 1000 * Frac(DateField / 1000) -1)

Datefield is the field that has the Julian date format

Thanks all!