Converting 4 or 7 digit julian date to calendar date?

[Shaun_Hornsby]

So i have a problem that i have been trying to figure out for awhile now. Some of the data that I am tracking contains Julian Dates when i paste the data in. It is currently in the format of YDDD where 9045 = 2/14/19 or 0105 = 4/14/20 . I have been using excel to convert these 4 digit Julian Dates into Calendar Dates using the following formulas.

For 2019:
=DATE(2010+LEFT(C1),1,RIGHT(C1,3))

for 2020:
=DATE(2020+LEFT(C1),1,RIGHT(C1,3))

This has been by far the fastest solution i had been able to find at the time to convert the date as all it takes is a few minutes to copy paste from airtable into excel and then copy paste into a different column in airtable the converted date from excel.

I have not found a viable solution to convert the 4 digit Julian Date to a Calendar Date directly in Airtable so i thought about converting that 4 digit Julian Date to a 7 digit Julian Date (YYYYDDD). by breaking down the 4 digit date into several different columns in Airtable and using additional columns for conversions i have achieved my goal of converting YDDD to YYYYDDD. But, I still have not found a solution that works to even convert that date to a normal calendar date.

I am trying to save myself a little more time on data entry by figuring this out but i am unsure at this point that I will be able to do this in Airtable alone.

Does anyone have any ideas on how to convert the 4 or 7 digit Julian Date to a Calendar Date without having to go back n forth between Airtable and Excel? I have tried countless different IF statements that i found searching the web and nothing works.

Here is a link to a sample base i have made with what i have so far converting the 4 digit date to a 7 digit date. The way i currently have it setup will only work for 2019 and 2020 BUT i have the If statement setup in “Julian Year 2” so that its easy to add 2021, etc.

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[simmonkc]

Probably a little late to this response, but you should be able to do this with an Airtable Automation + Script block.  I used ChatGPT to help write this script. Here is a video showing how I've used ChatGPT to help with these scripts.

Here is that code. Make sure the Input Variable name in your setup is convertedJulianDate.

// Get the input from the previous step
let record = input.config();

let julianDate = record.convertedJulianDate;

// Define the function to convert YYYYDDD Julian date to MM/DD/YY format
function convertJulianDate(julianDate) {
    // Assuming julianDate is a string in the format YYYYDDD
    const year = julianDate.slice(0, 4);
    const dayOfYear = julianDate.slice(4);

    // Convert day of year to MM/DD/YY
    const date = new Date(year, 0); // January 1st of the given year
    date.setDate(dayOfYear);

    // Format the date as MM/DD/YY
    const formattedDate = `${(date.getMonth() + 1).toString().padStart(2, '0')}/${date.getDate().toString().padStart(2, '0')}/${year.slice(2)}`;

    return formattedDate;
}

// Convert YYYYDDD Julian date to MM/DD/YY format
const formattedDate = convertJulianDate(julianDate);

// Save the formatted date as a variable output for future automation steps
output.set('formattedDate', formattedDate);