need julian-like dates

[papa]

I am trying to figure out how to enter a date in a cell
and have another cell tell me how many days between that
day and the Oct 1 preceeding the entered date. The
issues that I have that are causing me to stumble are:
for dates beyond 2/29 in leap years, and the fact that
2003 is not always the preceeding year in my application.

Any help would be much appreciated.

Papa

 

[Myrna Larson]

=A1-DATE(YEAR(A1)-IF(MONTH(A1)<10,1,0),10,1)

 

[JE McGimpsey]

Or, with one less function:

=A1-DATE(YEAR(A1)-(MONTH(A1)<10),10,1)

Though I suspect that as a "julian-like date" that 1 Oct 2004 should
return 1, not 0, which would be


=A1-DATE(YEAR(A1)-(MONTH(A1)<10),10,0)

 

[papa]

Neither of these work. The both return a result of
12/30/1900 or 12/31/1900.
My cell E199 is where the date gets entered in MM/DD/YYYY
format.
This is what I intered in E203 from the input below:
=E199-DATE(YEAR(E199)-(MONTH(E199)<10),10,1)

Maybe a better approach would be to simply count the
number of days from the previous 10/1 to the date entered
in E199.
But I still have the problem of being able to account for
the proper year and associated leap years.

Thanks,
Papa

 

[JE McGimpsey]

They both work.

Change the format of your cell from Date to General.

XL is trying to "help" - but is too stupid to realize that subtracting
one date from another should never result in a date.

 

[papa]

That was it - Thanks so much!

-----Original Message-----
They both work.

Change the format of your cell from Date to General.

XL is trying to "help" - but is too stupid to realize that subtracting
one date from another should never result in a date.


.