Day of year

J

Jimmy B

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?
 
N

Niek Otten

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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?
 
J

JLatham

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

Ruth

Jimmy B said:
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?
Click to expand...

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
 
D

David Biddulph

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
Click to expand...

Are you sure, Ruth? Won't that display 2007, rather than 96?

To get 96, you'll need Niek's formula:
=TODAY()-DATE(2007,1,0)
or =A1-DATE(2007,1,0) to convert a particular date.
 
M

MartinW

And following that
=TODAY()-DATE(YEAR(TODAY()),1,0)
Should work in any year.

HTH
Martin
 
J

JLatham

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

Ruth

Sorry, I wasn't paying enough attention. Type "yy" instead of "yyyy" and it
will give you the last 2 digits of the year
 
M

MartinW

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

Regards
Martin
 
D

David Biddulph

Yes it would, but that's 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
=A1-DATE(YEAR(A1),1,0)
 
J

JLatham

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

Dave Peterson

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:
=YEAR(TODAY())&"-"&TEXT(TODAY()-DATE(YEAR(TODAY()),1,0),"000")

(And I like 4 digit years!)
 
M

MartinW

Hi Dave and JLatham,

I still can't see the need for any extra

With this in a spreadsheet
=TODAY()-DATE(YEAR(TODAY()),1,0)
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.

Regards
Martin
 
J

JLatham

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
"julian days" go to 366, all other years only go to 365.

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

MartinW

Hi JLatham,

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.

Regards
MartinW
 
J

Jimmy B

Thank you, Martin. I believe this is exactly what I need. I'm not overly
concerned about the leap year stuff. This is needed to calculate rough times
of year for beach closures among the various beaches in Macomb County, MI.
It's meant to lead to averages for closure dates, standard deviations, and
t-tests to see if the time of the year for closings really does vary among
the various beaches. I was using a DATEDIF function, and that's ok for one
year, but when you have thousands of dates over a ten-year timespan, it
becomes a rather daunting prospect. This is a significant improvement over
the function I was using. Thanks again.
 
Top