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?

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jimmy B" <Jimmy wrote in message ...
| 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?

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

HTH
Martin

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

"MartinW" wrote:

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

HTH
Martin

Huh?
<bg



JLatham wrote:

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

"MartinW" wrote:

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

HTH
Martin




--

Dave Peterson

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


"Dave Peterson" wrote:

Huh?
<bg



JLatham wrote:

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

"MartinW" wrote:

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

HTH
Martin




--

Dave Peterson

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

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" wrote:

Huh?
<bg



JLatham wrote:

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

"MartinW" wrote:

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

HTH
Martin




--

Dave Peterson

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.

"MartinW" wrote:

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

HTH
Martin

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.

"Jimmy B" wrote:

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?

"Jimmy B" wrote:

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?

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

"Ruth" wrote in message
...

"Jimmy B" wrote:

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?

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

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.
--
David Biddulph