4 digit julian date

[centenaro]

Does anyone know the formula to convert a calendar date "=now()" to a 4 digit julian date JJJY?

[6StringJazzer]

You will have to give more definition about what you mean by Julian date. The Julian date is the number of days since January 1, 4713 B.C. For example, today's Julian date is 21108. A Julian date is not a four-digit number.

[Glenn Kennedy]

I have seen references to Julian dates with a 2 digit year component, and with a 4 digit year component... but never to one with a single digit year...

[ChemistB]

There are a lot of different definitions to Julian Date (i.e. # of days passed since January 1, 4713 BC). One such nomenclature is yyddd where yy is last 2 digits of year followed by day number of that year (i.e. April 19, 2021 = 21109 as 4/19 is the 109th day of 2021). What do the 4 digits in your Julian Date refer to?

[MrShorty]

In other contexts (older programming languages/contexts), I have heard of "Julian Date" referring to "day of the year" or what Wikipedia calls "ordinal date" (wikipedia's page also has some ideas for calculating ordinal date: https://en.wikipedia.org/wiki/Ordinal_date ). I think this use of "julian day" persists in spite of the confusion created between this understanding and the actual Julian calendar that the others are talking about.

Assuming some of the technical aspects mentioned in the Wikipedia article don't apply, one could conceivably calculate ordinal date in Excel by subtracting 31 Dec previous year from the date of interest. =NOW()-DATE(2020,12,31) to get the number of days since the end of the previous year, though I would probably use TODAY() rather than NOW() because NOW() includes time information (today at current time of day). It looks like you then want to concatenate the last digit of the current year to result =CONCATENATE(TODAY()-DATE(2020,12,31),1).

Depending on what exactly you are doing, YEARFRAC() might also be a useful function: https://support.microsoft.com/en-us/...rs=en-us&ad=us

As the others have said, help us understand exactly what you mean by "julian date" and we should be able to help you.

[centenaro]

Thank you all, I have it.

=TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")&TEXT(TODAY(),"yy")-20

Displays 1091 4-digit Julian Date


=TEXT(TODAY(),"yy")&TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")

Displays 21109 5-digit Julian date.

[6StringJazzer]

So what you want is

DDDY

where DDD is the day of year, and Y is the last digit of the year.

I guess that gets you there, but it will break for any year earlier than 2020 or later than 2029.

This takes a slightly different approach and will be good forever.

Please Login or Register  to view this content.

[centenaro]

I see, that's good to know. Thanks!!

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.