While I was answering one of the tableau Community questions, I came across this question where User want’s to convert Calendar Date to Julian Date. The requirement is shown below.
Let’s first understand what is Julian Date, why it is used and what is the Purpose of using it?
What is Julian Date?
Julian date is often confused with the Julian calendar, which was introduced by Julius Caesar in 45 B.C. to bring about reform in the Roman Empire. Julian date is the difference (or modulus) of time in days lapsed since the start of the 7,980-year cycle. This 7,980 years is calculated with several traditional time cycles (solar, lunar and a Roman tax cycle) for which 7,980 was a common multiple
Why is it Used?
> Julian date is commonly used in computer science to calculate the difference between days since all numbers in the system are consecutive integers.
> for easily calculating elapsed days between two events (e.g. food production date and sell by date.
Let’s use Tableau to convert Calendar Date to Julian Date, as per the question we need to convert Calendar Date to Julian (CYYDDD) and Modern Julian Date format (YYYYDDD)
C- How Many Centuries from 1900
YY- Year Within the Century
DDD- Day in the Year
YYYY- Year of the Date
Let’s Take an example sample Date 07/13/2018 (MM/DD/YYY).
We will see how we can break this Calendar state step by step to Julian Date ( CYYDDD )and Modern Julian Date ( YYYYDDD ).
Julian Date(CYYDD) Functional Concept:
Modern Julian Date (YYYYDDD):
How to Calculate Day of the year Check the below image:
Tableau Calculations you Need to use
With the above
I Hope it would be useful
Challenge and Solution: https://community.tableau.com/thread/278163