Excel - Day of year
Asked By Jimmy on 06-Apr-07 10:10 AM
Is it possible to get Excel 2003 to display a date as the day of the year?
For instance, today is 4-6-07, the 96th day of the year. How can I get the
spreadsheet to display 96?
Niek Otten replied on 06-Apr-07 10:20 AM
Microsoft MVP - Excel
HelpFrom replied on 06-Apr-07 10:22 AM
Search Excel Help for "Julian Date" - they show how to convert a date to
display as Julian. Their examples usually include including the year, but
it's easy enough to leave that off and display 001 for January 1st instead of
2007001 for Jan 1, 2007.
Rut replied on 06-Apr-07 10:22 AM
Highlight the cells that you want to display the year
Goto Format --->Cells---->in the Category section of the pop up box, select
Custom--->in "Type" box - type "yyyy" click ok
David Biddulph replied on 06-Apr-07 10:36 AM
Are you sure, Ruth? Won't that display 2007, rather than 96?
To get 96, you will need Niek's formula:
or =A1-DATE(2007,1,0) to convert a particular date.
MartinW replied on 06-Apr-07 10:39 AM
And following that
Should work in any year.
HelpFrom replied on 06-Apr-07 11:06 AM
Yes, as long as both dates are in same year, but if one date was after Feb 29
of a leap year and other year is not leap year...
Dave Peterson replied on 06-Apr-07 11:17 AM
Rut replied on 06-Apr-07 11:52 AM
Sorry, I was not paying enough attention. Type "yy" instead of "yyyy" and it
will give you the last 2 digits of the year
MartinW replied on 06-Apr-07 12:19 PM
Yeah I suppose a Philosopher may have an issue with whether today
is really today, and a Physicist may actually be able to prove that today
doesn't exist in real time, however, to a Mathematician I think today
would pretty much be today. ;-)
David Biddulph replied on 06-Apr-07 02:16 PM
Yes it would, but that is still not what the OP asked for, Ruth.
For the 96th day of the year he wanted to see 96, not 07, so he needs
T. Valko replied on 06-Apr-07 02:41 PM
Unless it is after midnight in which case today would actually be tomorrow.
Dave Peterson replied on 06-Apr-07 04:15 PM
And in just two days, tomorrow will be yesterday.
HelpFrom replied on 06-Apr-07 07:06 PM
Probably one of those "don't pay any attention to me today - I DID take my
meds!" days - been on pain killers for 2 days now. But March 1, 2007 is
Julian day 60 (31 in Jan, 28 in feb 59 + 1 = 60) But in Leap Years, March 1
is Julian day 61 (31+29+1) So you have to be talking about the same year if
you use TODAY for a comparison - if you happened to be speaking of a day in a
leap year but used a non-leap year to calculate Julian date, then the day
would be off by 1 if the date was after Feb 28 of that year.
Dave Peterson replied on 06-Apr-07 07:16 PM
So what you're saying is that the 60th day of any year doesn't always occur in
the same month.
That's a good reason to include the year:
(And I like 4 digit years!)
Martin replied on 06-Apr-07 08:40 PM
Hi Dave and JLatham,
I still can't see the need for any extra
With this in a spreadsheet
that you open on the 1st March 2007 your result would be 60.
If you open the same spreadsheet on 1st March 2008 your result would be 61.
Excels date system takes care of the leap year for you. It's one instance
where the volatility of the TODAY function works for you.
HelpFrom replied on 06-Apr-07 09:08 PM
I'm not arguing with your solution - it is spot-on for what the OP requested.
However, it cannot be used as a 'generic' what is the julian date for a
specific date - you have to consider the year for that, because Julian date
for all dates after Feb28 in leap year differ from non-leap years. Leap year
Dave - yes, the 60th day of the year is March 1st for non-leap years, it is
Feb 29 in leap years. And in leap years, I think the "last day of the month"
for all months after that get slipped in to "next month" if you get my
meaning. Like I said, in a leap year, Dec 31 is 366 - but for any other
year, there is no 366th day.
The solution offered works - but not as a generic solution. Suppose you had
something like that up on a web site to provide people with the Julian day of
the year: Enter Month/Day. And you use the NOW() or TODAY() functions to
determine it - it's going to give different results depending on whether or
not the current year is a Leap Year or not (and whether or not the date is
on/before or after Feb 28 of that year). So to be accurate with a generic
solution, you have to consider the year also, although you can ignore
displaying the year as part of the result.
Martin replied on 06-Apr-07 09:58 PM
It will work as a generic what is the Julian date, but only for the current
year. Excel takes care of the Leap Years.
Where it will fall down is if you want to know the Julian Date of future or
previous years, such as what is the Julian date for 2nd May 2003 etc.
HelpFrom replied on 07-Apr-07 07:52 AM
Exactly! That's what I was trying to get across.