Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Philip Gulan

# How to convert the Date to Julian Date by Formula?

I need to create a formula field to get the date from the Creation Date and convert it to Julian Date. any help is apperciated
3 Creeks
There are formulas here that tell you how to do that: http://aa.usno.navy.mil/faq/docs/JD_Formula.php
karthikeyan perumal
Hello

Create Formulafield with Text Datatype and add the formula below

Text((DAY(P_Date__c)-32075+1461*(YEAR(P_Date__c)+4800+(MONTH(P_Date__c)-14)/12)/4+367*(MONTH(P_Date__c)-2-(MONTH(P_Date__c)-14)/12*12) /12-3*((YEAR(P_Date__c)+4900+(MONTH(P_Date__c)-14)/12)/100)/4))

just Replace P_Date__c with your date field name.

hope it will work

Mark Best ANSWER if its work for you.

Thanks
karthik

Philip Gulan
Hi karthik,

Can you please update this for datetime type instead of date type

Thanks
Philip
karthikeyan perumal
Hello Philip

kindly use below code.

Text((DAY(DATEVALUE(DATETIME_FIELD__C))-32075+1461*(YEAR(DATEVALUE(DATETIME_FIELD__C))+4800+(MONTH(DATEVALUE(DATETIME_FIELD__C))-14)/12)/4+367*(MONTH(DATEVALUE(DATETIME_FIELD__C))-2-(MONTH(DATEVALUE(DATETIME_FIELD__C))-14)/12*12) /12-3*((YEAR(DATEVALUE(DATETIME_FIELD__C))+4900+(MONTH(DATEVALUE(DATETIME_FIELD__C))-14)/12)/100)/4))

kinldy replace your Datetimefielname  insted of  DATETIME_FIELD__C

Make best ANSWER if its solved.

Thanks
Karthik
Philip Gulan
Hi Karthik,

I tested the formula and what I get as a result for today's date is Julian Date 2457602.50875 which doesn't seem to be correct.
Are you sure about the formula?

Regards,
Philip
karthikeyan perumal
Hello Philip,

Use below new formula, This is the formula i got it from Wiki.. and i checked some other  sites also . its work fine.

Text((2-(YEAR(DATEVALUE(CreatedDate))/100) + ((YEAR(DATEVALUE(CreatedDate))/100)/4))+
DAY(DATEVALUE(CreatedDate)) +
(365.25*(YEAR(DATEVALUE(CreatedDate))+4716))+
(30.6001*(MONTH(DATEVALUE(CreatedDate))+1))-
1524.5)

Mark BEST ANSWER if its work fine.

Thanks
Karthik

Philip Gulan
Hi Karthik,

Thank you. but please note Julian Date is different to the formula you provided.

http://www.fs.fed.us/fire/partners/fepp/julian-calendar.pdf

As you can see today is 22nd September and according to the Julian Date it should be 265. while your formula gives 2457601.7809

Please see details below and you can also find this on Internet:
Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar).

karthikeyan perumal
Hello

I check from here

http://www.onlineconversion.com/julian_date.htm

and nd I got a formula from here..

http://quasar.as.utexas.edu/BillInfo/JulianDatesG.html

kindly check and let me know  if you have the correct formula

thanka
karthik