Julian Date

[Jonathan_S]

Hey Everyone,

Im wondering how I would Display the Julian date for each day in the Calendar, And also How to change the Currant day cooler to be more eye catching.

[Suvrutt_Gurjar]

Hi @Jonathan_S,

Not sure, if you are looking at a complete Julian date calendar, then following approach will not help you. However if you are looking at simply converting Gregorian dates to Julian dates, please explore if following helps.

Also please note that the expressions mentioned below convert date from Gregorian date to Julian date by a simple formula such that Julian date is the combination of two digit year (e.g20 for 2020 an 19 for 2019) followed by the serial number of the day in the year so (January 15th is 15th day of the year and January 31 is 31st day of the year). I tested the expressions for a bunch of dates in 2019, 2020, 2023 and 2024 and it works, but please do test more if you will use it. Also it may not work for all historical dates comparison between Gregorian and Julian dates. Finally, there may be a better, more efficient expression possible.

For today’s Julian date, say column name [Julian Today], please use an expression something like
NUMBER(TEXT(TODAY(),“YY”)&(IF(LEN(TEXT(HOUR(TODAY()- DATE(TEXT(“01/01/”&YEAR(TODAY()))))/24+1))=2, TEXT(0&(HOUR(TODAY()- DATE(TEXT(“01/01/”&YEAR(TODAY()))))/24+1)),TEXT(HOUR(TODAY()- DATE(TEXT(“01/01/”&YEAR(TODAY()))))/24+1))))

For converting any Gregorian date to Julian date, say column name [G to J], please use an expression something like below . Here [Gregorian Date] is any date type column in the app.

NUMBER(TEXT([Gregorian Date],“YY”)&(IF(LEN(TEXT(HOUR([Gregorian Date]- DATE(TEXT(“01/01/”&YEAR([Gregorian Date]))))/24+1))=2, TEXT(0&(HOUR([Gregorian Date]- DATE(TEXT(“01/01/”&YEAR([Gregorian Date]))))/24+1)),TEXT(HOUR([Gregorian Date]- DATE(TEXT(“01/01/”&YEAR([Gregorian Date]))))/24+1))))

For highlighting today’s Julian date, you may use a format rule such as
[Julian Today]=[G to J]

A sample test app results shown below

[Elijah_Magrane]

Any idea on how to adapt these formula for a leap year, like we are in now?

[Suvrutt_Gurjar]

Could you please elaborate?

Did you try the above expressions and faced any issues for leap year? or are you looking for some different requirement?

[Jonathan_S]

Coming back to this one now… long delay.

So I only need a 3 digit number. I will use this number to concatenate onto somthing else.

Use would select date.

The 3 digit number is the currant day number of the year.

See example.

https://drive.google.com/uc?export=view&id=1tRfkB7CqrhKlNeZjwdpyGF20vUsv7ttu

Only thing is the number always needs to be 3 digit.

1 would be 001.
10 would be 010
100 would be 100

[LeventK]

@Jonathan_S
@Suvrutt_Gurjar
How about utilizing it like this?

RIGHT("00"&DAY([Date]),3)

[Jonathan_S]

So I tried this, It looks like it gives me only the number based on the month, not the year

[Suvrutt_Gurjar · Accepted solution]

Hi @LeventK ,

Thank you. Your insights are always great. I believe your expression will reset every month. For example, it will return 001 on 1st dates of all months. For example 1 Jan, 1 Feb, and 1 Mar. Whereas as per my understanding Julian date for 1st Jan is 001, 1st Feb is 032 and 1st March is 061 ( Leap Year) and 060 (Non-leap year). Basically, Julan date seems to be elapsed days from 1st Jan of that year.

Hence year and date of 1st Jan of the current year come into expression.

The following expression will return the 3 digit Julian date for all years

RIGHT(“00”&(HOUR([Order Date]- DATE(TEXT(“01/01/”&YEAR([Order Date]))))/24+1),3)

I optimized my earlier suggested expression few months ago for adjusting digit length by deploying your idea of using the RIGHT(…, 3) part of the expression.

Of course, I am sure, the expression can possibly be further optimized.

@Jonathan_S

My testing returns below Julian dates as in the image on various dates with the expression
[Order Date] is input and JDAYANY2 is the converted Julian date.

RIGHT(“00”&(HOUR([Date]- DATE(TEXT(“01/01/”&YEAR([Date]))))/24+1),3)

Please test well as dates and time logic is always a bit cumbersome.

[Jonathan_S]

hey,

Whats the differance with

RIGHT(“00”&(HOUR([Order Date]- DATE(TEXT(“01/01/”&YEAR([Order Date]))))/24+1),3)

and

RIGHT(“00”&(HOUR([Date]- DATE(TEXT(“01/01/”&YEAR([Date]))))/24+1),3)

[Suvrutt_Gurjar]

No difference

I removed [Order Date] that I had used in my testing and replaced it with generic name [Date].

[Jonathan_S]

This seems to have done it. This is outstanding
Will need to keep an eye on it and test.

[Suvrutt_Gurjar]

Thank you. Good to know it meets the needs. In general, I have tested it for a number of dates as shared. But yes, it is always better to test extensively with date and time logic.

[Jonathan_S]

Can you explain the logic of this formula, It works. But would like to understand it. Like why divide by 24? etc

[Suvrutt_Gurjar]

Expression:

RIGHT(“00”&(HOUR([Date]- DATE(TEXT(“01/01/”&YEAR([Date]))))/24+1),3)

Explanation

1. DATE(TEXT(“01/01/”&YEAR([Date]))) - This part returns the 1 st Jan of the year of [Date]
   Julian dates give elapsed days from 1st of Jan. We need to find number of days from 1st Jan of that year.

2. HOUR([Date]- DATE(TEXT(“01/01/”&YEAR([Date]))))/24 is equivalent to

HOUR ( [Date]- [1st Jan Date]) / 24 is the way number of days are found between two dates.
Let us call it elapsed days. So it finds the number of days of [Date] from 1st Jan

3. HOUR([Date]- DATE(TEXT(“01/01/”&YEAR([Date]))))/24+1 is equivalent to
   Elapsed Days +1
   We need to add 1 because since we subtract 1st Jan , that day needs to be added back or else result will be one day less.

4. RIGHT(“00” …, 3) always ensure the result is 3 digit. This part we used @Levent’s method to convert it to three digits always.

Hope this helps.

[Jonathan_S]

Thank you very much for the in depth reply.

What Im really looking for is in the calendar view of the calendar. Underneath lets say January 20th, for it to have the number 20, the 30th the number 30, etc for the entire year for all years. Whatever year you go to. This would be in each cell in the month view, the top beside the actual date in the day view, etc.

[Suvrutt_Gurjar]

Thank you @Jonathan_S for your update. I understood that you wish to have a comprehensive Julian calendar. I am afraid, I am unaware how a Julian calendar can be inserted in an app.

Someone else in the community may have an idea to achieve it.

[linlyons]

I've been using Julian date for 50+ years.
I would like to see it in the basic google calendar.   Or at least an option to display it.
Like the option to display week #.
I don't want to calculate it.
When I see a reference from someone else, I'd like to look at today's Julian date so I can do a quick mental calculation about how long ago (or in the future) that date refers to.

While there may be lots of folks who use week #, I expect that there are more that would find Julian date handy w/o jumping through hoops.
I don't understand why someone would suggest that lots of folks each write their own routine to calculate the day of year, when if just 1 person did it, and posted it, the rest of us could benefit.
Also, if it's there, I expect that more folks would look at it and realize that  ### is easier than mm/dd  or is it dd/mm  or is it yy..  you get the picture.
Now that we're in the 21st century,  yyddd is concise, and easy to deal with.  yy.ddd  if you'd like.
Down in the lower corner would be my choice, if I had one.