Inane Coding Go on, slap that wayward code

19Oct/125

Converting Excel date to Julian day-of-year number

I recently came across a mini-puzzler while helping a friend with Excel. I needed to convert an Excel 'Date' data type (see below for a recap) to the equivalent day-of-year number. This is sometimes called the 'Julian date', or more strictly correctly, 'ordinal date', as the famous Wikipedia informs me.

Now, this seemed simple - I thought - and I suppose it still is, relatively. But there is no single function where you can just plug in an Excel date and get out the day of the year, as I was expecting there would be. For example, to plug in 2011/08/25 or 25th August 2011 and get out '237' as the day number of that year. (By the way; if you just want to calculate the day of the year for a one-off I found this calculator was good).

First, lets take a little look at the basics of how Excel stores dates, and then I'll go through the ways I found of doing this task;

 

Excel 'Date' data type

An important thing to know is that Excel stores dates as the integer number of days since 1st January 1900. For example, today (2011/08/25) is stored as the integer 40780. I'll briefly pass you over to a couple of paragraphs from the relevant Microsoft knowledgebase article as I think it does a very good job of explaining this:

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

Now that you're all clued up, we'll concentrate on the integer portion of the number and how this date can be converted easily.

 

How to actually do it

You could of course just put the current date in one cell, and the date of last years New Years Eve in another and then subtract the two, but that isn't very future-proof as you have to update it every year.

A better way was one I found at http://spreadsheetpage.com/index.php/tip/calculate_the_day_of_the_year_and_days_remaining/;

=A1-DATE(YEAR(A1),1,0)

... with the date you want to convert in cell A1. The function YEAR() will return just the year portion of its input. The DATE() function takes 3 inputs, year, month, day (in that order). So putting the output of the YEAR() function as the first input of the DATE() function will take care of one of these. Then putting a month of '1' will set it to January.

Here's the small clever part - using '0' for the day number. Doing so actually makes it use the day before the 1st. Why, thats 31st December of the year before! So, the code above simply subtract this date from the date which you put in cell A1, to give the day of year number (which Excel calculates).

Note: Make sure the cell with this formula is formatted in 'General' or 'Text' modes or else Excel won't output a number! You could alternatively pass it through a function such as TEXT() which would sort it out.

Modifying this to use the current date from the TODAY() command, instead of a date in cell A1 is probably more useful though, this givesfinal solution;

=TODAY()-DATE(YEAR(TODAY()),1,0)

This is the correct code and takes account of leap years...

 

The other code which I thought was needed but actually wasn't!

I first thought that the code above didnt take account of leap years, so I was working out a function to do this to add in, but it does so the code below isn't needed - but here it is anyway!

I was reading the comments on a http://www.mrexcel.com/archive/Formulas/7749.html, and found the following snippet (the rest of the code is not relevant as its for a different purpose);

=ISNUMBER("2/29/"&YEAR(A1))

This is meant to return TRUE of FALSE depending on if the year is a leap year (if the 29th of february exists) - however it didn't work for me (just always returned FALSE). But this gave me the right train of thought and I came up with the following which does the same thing as the previous piece of code was meant to;

=ISNUMBER(DATEVALUE("2/29"))

This uses the DATEVALUE() function to produce a date value of the 29th February. This returns a number if this day exists, and returns an error if it doesn't. The ISNUMBER() function converts this to TRUE or FALSE. Note: I had to ommit the current year from the input to DATEVALUE() as it caused the function to break - according to the help file this should not happen! As it is the function assumes the current year, which is fine for our purposes.

After finding this I also found the http://support.microsoft.com/kb/214019;

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")

This outputs text and uses cell A1 as an input (of the form 'yyyy' e.g. '2012') - but could easily be modified to give a TRUE / FALSE output or use the current year as the input. This formula looks like its based on the mathematical definition or similar, though I'm no expert. This is probably the best way of doing this particular task.

Thats it folks!

 

Share
Comments (5) Trackbacks (1)
  1. Thanks for your smart funcion, it is better than those complex macros.

    1) =A1-DATE(YEAR(A1),1,0) works, I just need copy in the cell and change A1 into the cell where my data comes from.
    2? =TODAY()-DATE(YEAR(TODAY()),1,0) not works after I copied it into the cell and it shows 6-Feb-00, I even do not know where to input my data so to caculate leap years.

    Thanks. I like your explain about the ‘Excel stores dates as the integer number of days ‘ that makes me know how to understand the function and change it. Your way is far better than http://www.mrexcel.com, http://support.microsoft.com/kb/116281/en-us, http://www.cpearson.com/excel/jdates.htm…those high rank webiste.

    I will appreciate your reply. I need them to analysis 1000 data about birds migration, you help me save lots of time and increasing my accuracy by avoiding hand caculation

    Zira Wang

    • I have just made a new *.xlsx file, and put =TODAY()-DATE(YEAR(TODAY()),1,0) in one of the cells, and it gives the result ’37’ straight away which is correct as the 6th Feb is the 37th day of the year. What is the cell formatted as? When you format as a number or as general text what does it show?

  2. I have a spread sheet that uses multiple Julian dates, the Julian dates are based off the actual date and then +1 is used to add a day to the JD. my problem is when i get to the end of the year the JD wont go to JD 001,002,003. Whould anyone know a formula that i could use to do this
    I currently use
    =A2-DATE(YEAR(A2),1,0)
    =H5+1 to have the JD advance in the next cell.

    Sunday, December 27, 2015

    361 362 363 364 365 365 366
    SUN MON TUES WED THURS FRI SAT

  3. How about having the A2 reference change to perform the increment, instead of adding 1 to it? i.e. have an incrementing column of dates, and use your formula next to it, but with the input A1, A2, A3, etc using the corresponding row from the column of dates. That way when you get to a day which is 2016, say, instead of 2015, the YEAR(xx) part of the formula will pick the next year to base the calculation on and so start again at 1.

    Alternatively you could use =MOD(xx-1,365)+1 where ‘xx’ is the output of your previous formula, which would take the previous output and start again at 1 once it got to the 366th date – but if you just want a straight series then you may as well just generate the numbers 1-365 then 1 again in the first place.

  4. Brilliant! It works in Open Office Calc as well, as long as you change the commas to semicolons, thus:

    =TODAY()-DATE(YEAR(TODAY());1;0)


Leave a comment

CAPTCHA human-ness test: *