Convert Date to Julian Date Format

[kozura]

Hi,

Just wondering if anyone has a simple way to convert a date that a user selects into Julian date format.

I have a need to create a simple Batch Code using Jullian date format and the concatenated with some other values.

I have an Excel and Google Sheet formula that works but when trying to use it in Glide using the “Excel Code” functionality it doesn’t work.

I have also looked at the various date and time functions but can’t find a solution there either. I even tried formatting the date using luxon but I can’t seem to find the functionality there either!

My work around is to use a sheet instead and have the formula in there but I have noticed quite a delay in updating my application with the output. Because of this, I run the risk of a user recording the wrong Batch Code because the app has not updated in time.

Hence I would like to drop the sheet completely and have the functionality all within Glide Tables.

Any help here will be greatly appreciated!

[Darren_Murphy]

Just to be clear, are you looking for a true Julian Date, or just an Ordinal Date? (yyyyddd). I ask because people often refer to ordinal dates as julian dates.

If an Ordinal date will do, then the following will work in a Math column:

YEAR(Date) * 1000
+ FLOOR(275 * MONTH(date)  / 9)
- (FLOOR((MONTH(date) + 9) / 12)
* (1 + FLOOR((YEAR(date) - 4 * FLOOR(YEAR(date) / 4) + 2) / 3)))
+ DAY(date)
- 30

Credit to @jeff_hager for the above

[kozura]

Hi Darren,

Thank you for your help.

The Ordinal Date might work nicely. Let me try that instead!

Much appreciated!

[kozura]

Hi Darren,

Thank you again for your solution, it works well for my purpose. My only ask is if it is possible to drop the first two characters of the output?

The date today outputs a value of 2022210, is it possible to reduce this to 22210 without further manipulation in Glide?

I don’t understand the formula used so not sure how to change it to get the result I need.

Thanks again.

[Jeff_Hager]

Just subtract 2000000 from that math result. You can add the subtraction to the end of the same formula in the same math column.