Formula to Convert Calendar Date to Julian Date

[#1]

Hi All, I need a formula to convert Calendar Date (column is in Character/Nominal Type, dd/mm/YYYY format) to Julian Date (numeric). For example, in Row#1 from 07-Mar-2024 (07/03/2024) to 24067:

Thank you!

Alex.

[#2]

Google search:   'calendar date to julian date formula'

The formula to convert a Gregorian date to a Julian date is: JD = 367K - (7(K + (M + 9)/12))/4 + (275M)/9 + I + 1721013.5 + UT/24. Where K is the year, M is the month, I is the day, and UT is the universal time in hours. The last two terms in the formula add up to zero for dates after 1900 February 28. [1]

Explanation of the variables: [1]

• K: Represents the year in the Gregorian calendar.
• M: Represents the month. January and February are treated as the 13th and 14th months of the previous year, respectively. For example, if the date is January 15, 2025, then M = 13 and K = 2024. If the date is February 20, 2025, then M = 14 and K = 2024.
• I: Represents the day of the month.
• UT: Universal Time in hours.
• JD: The resulting Julian Date. [1]

Steps for calculation: [1]

1. Determine K: If the month is January or February, subtract 1 from the year. Otherwise, use the given year.
2. Determine M: If the month is January or February, use 13 or 14 respectively. Otherwise, use the actual month number.
3. Substitute the values of K, M, I, and UT into the formula.
4. Calculate the Julian Date (JD). [1]

Generative AI is experimental.

[1] https://aa.usno.navy.mil/faq/JD_formula

The link appears to be where the majority of the AI generation originated from.

[#3 · Accepted solution]

Local(
	{date = InFormat(:Calendar Date, "Format Pattern", "<DD>/<MM>/<YYYY>")},
	Num(Right(:Calendar Date, 2) || Right("00000" || Char(Day Of Year(date)), 4))
);