dates

[mark]

is there a function that will take the date of 7/1/03 and
return 181 as the day number. This needs to disregard the
year. So 7/1/01 will still return 181. Currently Im
converting the dates to the current year than subtracting
it by 1/1/03.

ahelp

thanks

 

[Paul]

is there a function that will take the date of 7/1/03 and
return 181 as the day number. This needs to disregard the
year. So 7/1/01 will still return 181. Currently Im
converting the dates to the current year than subtracting
it by 1/1/03.

ahelp

You can use
=A1-DATE(YEAR(A1),1,1)
to get 181 from either 7/1/03 or 7/1/01 (US-style).

However, the day number within the year for these dates should be 182, not
181. (Think of 1/1/01 minus 1/1/01, which is 0 - but it is day 1.) So really
you need
=A1-DATE(YEAR(A1),1,1)+1

 

[Daniel.M]

However, the day number within the year for these dates should be 182, not
181. (Think of 1/1/01 minus 1/1/01, which is 0 - but it is day 1.) So really
you need
=A1-DATE(YEAR(A1),1,1)+1

You meant:
=A1-DATE(YEAR(A1),1,1)-1
or
=A1-DATE(YEAR(A1),1,)

Regards,

Daniel M.

 

[Guest]

another way is

=datedif(d1;d2;"yd") where d1 and d2 are dates, and d2>d

For example
d1= 01-01-X
d2= 12-12-Y
datedif(d1;d2;"yd")=345 where XX and YY are two equal or not equal years

For more information, please visit

http://www.cpearson.com/excel/datedif.ht

Regards

aorchard