DOnt calculate leap year

[kckar]

I need a formula that counts leap years as 365 days instead of 366 days



My work sheet is set up as follows:

c10=6/6/2003
f10=06/15/2003
h10=f10-c10

c13=6/15/2003
f13=6/6/2004
h13=f13-c13


i need the sum of h10 and h13 to always equal 365 even if it is a lea
year


other things that may help you understand this worksheet:
c9=prior anniversary date
f9=calculation date
h9=low days
c12=calculation date
f12=next anniversary date
h13=high days

and i need high days and low days to always equal 365

i need excel to never calculate a february 2

 

[Bob Phillips]

Don't get what you are asking. By my calculation, h10 has nothing to do
with leap years, and comes to 9 days, h13 comes to 357, so any leap years is
not taking it beyond 365.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Niek Otten]

Please don't post to more than one group in separate postings.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Harlan Grove]

kckar wrote...

I need a formula that counts leap years as 365 days instead of 366 days.

My work sheet is set up as follows:

c10=6/6/2003
f10=06/15/2003
h10=f10-c10

c13=6/15/2003
f13=6/6/2004
h13=f13-c13

i need the sum of h10 and h13 to always equal 365 even if it is a leap
year

....

OK, so what's wrong with

=MIN(365,H10+H13)

?

 

[Dave Peterson]

I put my larger date in A1 and my smaller date in A2 and used this formula:

=A1-A2-SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"mmdd")="0229"))

 

[kckar]

i need february 29th to be factored out so that t would be 9+356=365 no
just automatically making 9+357=36

 

[Dick Kusleika]

Dang! 30 minutes wasted on a worse formula

=A1-A2-SUM(--ISNUMBER(DATEVALUE("2/29/"&IF(YEAR(A1)=YEAR(A2),IF(OR(MONTH(A2)>2,MONTH(A1)<=2),2003,YEAR(A1)),ROW(INDIRECT("A"&(YEAR(A1)-1+(MONTH(A1)>2))&":A"&(YEAR(A2)+(MONTH(A2)>2))))))))

 

[Dave Peterson]

Somebody used the row(indirect()) just a couple of days ago.

I was lucky that I remembered!

 

[kckar]

thank you

sorry for the trouble
it works perfectly as far i can see right now

kace