Microsoft Excel vs the Julian Date

I had a need, so I created a solution. My solution might not be the best or the simplest solution but it works for me and you are welcome to use it if it will work for you too. I have searched several times online to find a simple way to convert a date in Microsoft Excel to a Julian date. I have not found a way that works for me yet, until now.

All I did was manually entered the Julian calendar into an excel spreadsheet. There are two separate Julian date calendars because of the fact that Leap years throw off the numbers. The source file can be found here. I inserted some conditional formatting that changes the colors of the calendars from shades of green to shades of red to signify which calendar is being used.

The form is not locked so feel free to poke around and see how the formulas determine the Julian date. I will explain this anyhow because I am bored out of my mind at the moment.
I had to strip the individual parts out of a date field.

A38 is =NOW() which displays the current date.
B38 is =DAY(A38) which extracts the day field from the date
C38 is =TEXT(A38, "Mmm") which extracts the 3 character month as text from the date field
D38 is =RIGHT(YEAR(A38),2) which takes the year from the date field and trims off the century

Now that we have the date all chopped up and usable lets use those pieces to get us a JDATE.
The formula in B40 is =INDEX(A4:M35, MATCH(B38,A4:A35,0), MATCH(C38,A4:M4,0))
Basically that uses the Day and the Month to find the date in the grid for the perpetual calendar.

Now that we have extracted the Day of the year for the JDATE we can concatenate that with the year to get our standard 5 digit JDATE. I know there are different ways to display a Julian date so feel free to hack it up and concatenate it however you need it done. The formula for the structure I need is split by an IF statement to make sure that I maintain a 5 character JDATE. Basically if the day is less than 100 it concatenates a zero in between the year and the day, otherwise it just concatenates the year and the day.

D40 is =IF(B40<100,CONCATENATE(D38,"0",B40),CONCATENATE(D38, B40))

To automatically check to see if the year is a leap year I added another IF statement in I38 that includes an HLOOKUP (horizontal lookup) to check the contents of an array of years that I listed at the top of the leap year calendar. This form will be accurate until the year 2060. I hope I don't care about this by then :D

I38 is =IF(H40="",IF(ISNA(HLOOKUP(YEAR(A38),$O$3:$AA$3,1,FALSE)),"No","Yes"),IF(ISNA(HLOOKUP(YEAR(H40),$O$3:$AA$3,1,FALSE)),"No","Yes"))

In cell H40 You can delete the date to see the Julian date for today or you can enter any date and the form will return the Julian date for that day.

I hope this is helpful to someone some day. If not at least I know where I can find it again. Happy converting :)

~The Phubar, out...