function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Philip GulanPhilip 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 Creeks3 Creeks
There are formulas here that tell you how to do that: http://aa.usno.navy.mil/faq/docs/JD_Formula.php
karthikeyan perumalkarthikeyan 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 GulanPhilip Gulan
Hi karthik,

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

Thanks
Philip
karthikeyan perumalkarthikeyan 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

Hope it will help you 
Make best ANSWER if its solved. 

Thanks
Karthik
Philip GulanPhilip 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 perumalkarthikeyan 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 GulanPhilip Gulan
Hi Karthik,

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

Please refere to this file:
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). 

User-added image
karthikeyan perumalkarthikeyan 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