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).

Share