Day of the year

  • Thread starter Thread starter norm801
  • Start date Start date
N

norm801

Simple question.....but I'm at a loss!

How can I get the day of the year from a date?

Example, January 31st would be 31
December 31 would be 365 (except in leap year)

Thanks!

Norm
 
Norm,

Use DateDif, and force a date for the end of last year. like so

=DATEDIF(DATE(YEAR(E13)-1,12,31),E13,"d")


This assumes your date is in E13

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
With your date in A1

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

also note that you have to format the result as General, excel has a
tendency to be helpful and when dates are involved
it will return the date 366 days after 01/00/1900 so just format as General
(easiest way is to select a cell, click the paintbrush button in the menu
bar and then click the result cell)
 
Hi Norm; I'll assume yor dates are in column A, so make
B1=A1-EOMONTH(A1,-MONTH(A1))

Hope that does what you want:)
 
Hi

=DATEDIF(DATE(YEAR(YourDate),1,0),YourDate,"d")
or
=YourDate - DATE(YEAR(YourDate),1,0)
where YourDate is a valid reference to cell with date, or a valid function
returning a date.
 
Hi Don!

Unless the year is 1900! <vbg>

="31-Dec-1900"-DATE(1900,1,0)
Returns 366
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,
I don't know if you should admit that you remember that year as a leap year.
 
Don Guillett schrieb am 12.01.2004 16:46:
Norman,
I don't know if you should admit that you remember that year as a leap year.
Click to expand...
Sorry to say, but 1900 is/was _not_ a leap year

=OR((MOD(year,400)=0),AND(MOD(year,4)=0,MOD(year,100)<>0))

\/\/erner
 
Werner Flamme said:
Don Guillett schrieb am 12.01.2004 16:46:
Sorry to say, but 1900 is/was _not_ a leap year

=OR((MOD(year,400)=0),AND(MOD(year,4)=0,MOD(year,100)<>0))

\/\/erner
Click to expand...

You are quite correct: 1900 was not a leap year. The point of the (humorous)
posts was that Excel considers that 1900 WAS a leap year! As I understand
it, Microsoft knew this was incorrect when they designed Excel, but
considered it commercially more important to achieve compatibility with a
rival spreadsheet (in which this was simply a mistake) than to be strictly
correct.
 
Back
Top