need julian-like dates

  • Thread starter Thread starter papa
  • Start date Start date
P

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


.
Click to expand...
 
Back
Top