day number in a year

P

Peter B

Does anyone know how to find which day in a year a date
is. Ie. 1st of February is always 32 in any year. Is
there any formula I can use to get this?

Peter B
 
P

Paul

Peter B said:
Does anyone know how to find which day in a year a date
is. Ie. 1st of February is always 32 in any year. Is
there any formula I can use to get this?

Peter B
Click to expand...

For a date in A1:
=A1-DATE(YEAR(A1),1,0)
 
P

Peo Sjoblom

With the date in A1

=DATEDIF(DATE(YEAR(A1),1,0),A1,"d")

OR

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

Norman Harker

Hi Peter!

Try:
=A1-DATE(YEAR(A1)-1,12,31)
Or:
=A1-DATE(YEAR(A1),1,1)+1
Or:
=A1-DATE(YEAR(A1),1,0)

This third alternative works because Excel treats the 0th of a month
as being the last day of the previous month.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
2

2rrs

Peter B said:
Does anyone know how to find which day in a year a date
is. Ie. 1st of February is always 32 in any year. Is
there any formula I can use to get this?

Peter B
Click to expand...

Perhaps there is a reason this has not been suggested but it seems to
work on my sampling:

with your date in A1

enter in B1, =(A1-"12/31/03")

format as general
 
N

Norman Harker

Hi 2rrs!

Re: "Perhaps there is a reason this has not been suggested but it
seems to work on my sampling:"

The reason is that entry of dates in string form is discouraged
because of problems that arise with different Regional Settings.

I get the dreaded #VALUE! when I use your formula because I'm on
English (Australian) where the format is dd-mm-yy and Excel throws a
wobbler because there isn't a month 31. There is also a problem if
some darn fool's been at the double digit year interpretation setting
and in that case it could interpret the date as 1903.

Excel Help in 2002 said: "Problems can occur if dates are entered as
text." Excel 2003 "much improved" Help doesn't appear to have this
warning.

Mind you, a lot of Excel Help examples enter dates in text form!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
news:[email protected]...
 
2

2rrs

Hi Norman,

Thank you for the helpful reply.
Perhaps a "Named Cell" in lieu of "12/31/2003" would have been a
better suggestion. Enter the start date in the named cell. This,I
believe would result in a numeric value in accordance with regional
settings.

Thanks again, 2rrs
 
N

Norman Harker

Hi 2rrs!

Definitely the best way!

1. It isn't a string date, so you don't hit the Regional Settings
problems.
2. It gives greater ease of amending the date without screwing up the
formula.

In passing. I find it a retrograde step that Help on date functions
omits the warnings on string dates that were given in Excel 2002.
Retrograde? Yes! We have gone backwards to 2000 and before. It's made
worse because in many of the Help Examples on Dates and other
functions they use String dates. No wonder people outside the US get
problems.

You can use a string date form but it has to be one of the
unequivocally recognised forms:

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Note that all use 4 digit years and prevent probs if some darn fools
been at the double digit year interpretation. The 7th one is the
ISO2000 approved separated form.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top