Excluding leap days in a formula

  • Thread starter Thread starter PMBO
  • Start date Start date
P

PMBO

Is there an excel function which could subtract (-1) from a formula if the
dates included the leap day? I don't want to count that one additional day
in a leap year; I know I can include a -1 in my formula, but I'm looking for
something that will decide if it's a leap year and automatically subtract it
from my date difference formula.
 
Is there an excel function which could subtract (-1) from a formula if the
dates included the leap day? I don't want to count that one additional day
in a leap year; I know I can include a -1 in my formula, but I'm looking for
something that will decide if it's a leap year and automatically subtract it
from my date difference formula.
Click to expand...

Your formula can be altered to not count Feb 29. Post your formula and someone
will show you how to account for that.
--ron
 
I'm not sure why you are using =DATEDIF(A1,A2,"d") instead of =A2-A1, but it
may be worth trying:
=(YEAR(A2)-YEAR(A1)-((YEAR(A2)-YEAR(A1))>DATEDIF(A1,A2,"y")))*365+DATEDIF(A1,A2,"yd")You may, of course, be able to simplify that.I assume that if there is more than one Feb 29 in the period, you want tosubtract one for each?--David Biddulph"PMBO" <[email protected]> wrote in messageRon -> I'm using a simple datedif =DATEDIF(A1,A2,"d")>> "Ron Rosenfeld" wrote:>>> On Thu, 11 Dec 2008 05:05:00 -0800, PMBO <[email protected]>>> wrote:>>>> >Is there an excel function which could subtract (-1) from a formula ifthe>> >dates included the leap day? I don't want to count that one additionalday>> >in a leap year; I know I can include a -1 in my formula, but I'm lookingfor>> >something that will decide if it's a leap year and automaticallysubtract it>> >from my date difference formula.>> >>>>> Your formula can be altered to not count Feb 29. Post your formula andsomeone>> will show you how to account for that.>> --ron>>
 
You're right - I changed it to the simplier formula, but I tested your
formula on dates 2/1/2008 to 3/1/2008 = 29 days and 2/1/2009 to 3/1/2009 = 28
days. I don't want it to count that leap day. Any other ideas?
 
Ron -
I'm using a simple datedif =DATEDIF(A1,A2,"d")
Click to expand...


This should work:

=SUMPRODUCT(--(((MONTH(ROW(INDIRECT(StartDt&":"&EndDt)))<>2)+
(DAY(ROW(INDIRECT(StartDt&":"&EndDt)))<>29))>0))

--ron
 
Fantastic - It works perfectly. Thank You Ron.

Ron Rosenfeld said:
This should work:

=SUMPRODUCT(--(((MONTH(ROW(INDIRECT(StartDt&":"&EndDt)))<>2)+
(DAY(ROW(INDIRECT(StartDt&":"&EndDt)))<>29))>0))

--ron
Click to expand...
 
This should work:

=SUMPRODUCT(--(((MONTH(ROW(INDIRECT(StartDt&":"&EndDt)))<>2)+
(DAY(ROW(INDIRECT(StartDt&":"&EndDt)))<>29))>0))

--ron
Click to expand...

A quick note -- the above formula returns an inclusive count. You should
probably subtract one (1) to obtain the result you really want.
--ron
 
Back
Top