Date Calculation

[Guest]

I would like to calculate the number of days within a span of years without
February 29th. In other words, I don't want to recognize the 29th day of
February within leap years.

 

[T. Valko]

Do you have a start date and an end date?

A1 = start date = 1/1/1986
B1 = end date = 1/1/2007

=B1-A1-SUMPRODUCT(--(MONTH(DATE(ROW(INDIRECT(YEAR(A1)&":"&YEAR(B1))),2,29))=2))

I'm sure there's a nice short algorithm for determining leap years based on
year.....but I don't know what it is! I've read it somewhere.

Biff

 

[T. Valko]

P.S.

Format the cell as GENERAL

I'm sure there's a nice short algorithm for determining leap years based on
year

There's more to it than just finding years divisable by 4.

Biff

 

[T. Valko]

Ooops!

Disregard that formula. I don't take into account the full start, end dates
for the leap year. Back to the drawing board! I have to step out for a few
hours. If there are no other solutions I'll come up with something when I
return.

Biff

 

[T. Valko]

OK, try this:

A1 = start date
B1 = end date

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"mdd")<>"229"))

Instead of counting how many leap days are in the range and subtracting we
can just count how many days *are not* leap days.

Biff

 

[Ron Rosenfeld]

I would like to calculate the number of days within a span of years without
February 29th. In other words, I don't want to recognize the 29th day of
February within leap years.

Try this:

=A2-A1-SUMPRODUCT((MONTH(ROW(INDIRECT(A1&":"&A2)))=2)*
(DAY(ROW(INDIRECT(A1&":"&A2)))=29))


--ron