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.
