Calendar Functions

These functions are used to convert between various astronomical calendars, the calenders available are…

  • Julian date; the number of days since 12:00 1st January 4713BC
  • Excel native date system where 1 = 00:00 on the 1st January 1950
  • J2000; the number of days since 12:00 on the 1st January 2000

Most functions that require a date currently need it in the J2000 format so you will find yourself using the days2000() function quite a bit!

Be careful with dates in Excel, the date is stored as a number, days since Jan 1st 1959 are represented as integers and any fraction is the fraction of the day (time to you and me) – 0.5 is midday in excel – However just because Excel is showing a date doesn’t mean that the time element wont be calculated, so if you only want the date part of an Excel date it is best to use =int(date serial number) to remove any fractions of the date representing the time. Formatting cells in Excel is quite powerful if you want to display the date and time then use custom formatting.

Example: Excel date and time formatting
Excel date and time formatting

If your function returns a date in excel format you will need to format the cell to show the date the custom formatting I like the most and commonly used by astronomers is yyyy/mm/dd hh:mm:ss for midday on the 1st January 2000 this would give 2000/01/01 12:00:00

All dates in calculations will assume UT (GMT) so you will need to add the correction necessary for your area of the world and any daylight saving.

Calendar conversion functions

gst(j2000) returns siderial time at longitude 0 for given days after j2000
jDay(Year,Month,day,Hour,Minute,seconds,Gregorian Flag) converts a date and time into Julian day Number, can use Gregorian(default) or Julian dates
Gregorian Flag = 1 or missing = use gregorian dates; if – 1 = use Julian Dates
JdaytoDate(Julian Date) converts Julian day number into excel date serial number
days2000(Year,Month,day,Hour,Minute,seconds,Gregorian Flag) find the number of days since 01/01/2000 can use Gregorian(default) or Julian dates
Gregorian Flag = 1 or missing = use gregorian dates; if – 1 = use Julian Dates
J2000toDate(days afterJ2000) Converts a J2000 number back to an Excel date index
Tip, format cell as date or time or both to see the date in a specific format
JDE_to_J2000(JulianDate) Convert a julian date to a J2000 date
J2000_to_JDE(J2000) Convert a J2000 date to a Julian date
Exampe: AstroExcel calendar conversion
Using AstroExcel to convert between various calendars
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s