Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 15, 2020 07:59 PM
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.
Jan 06, 2024 08:27 AM - edited Jan 06, 2024 08:31 AM
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);