Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
On Thu, 11 Dec 2008 05:05:00 -0800, PMBO
wrote: 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. Your formula can be altered to not count Feb 29. Post your formula and someone will show you how to account for that. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
Ron -
I'm using a simple datedif =DATEDIF(A1,A2,"d") "Ron Rosenfeld" wrote: On Thu, 11 Dec 2008 05:05:00 -0800, PMBO wrote: 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. Your formula can be altered to not count Feb 29. Post your formula and someone will show you how to account for that. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
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" wrote in ... Ron - I'm using a simple datedif =DATEDIF(A1,A2,"d") "Ron Rosenfeld" wrote: On Thu, 11 Dec 2008 05:05:00 -0800, PMBO 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
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? "David Biddulph" wrote: 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" wrote in ... Ron - I'm using a simple datedif =DATEDIF(A1,A2,"d") "Ron Rosenfeld" wrote: On Thu, 11 Dec 2008 05:05:00 -0800, PMBO 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
On Thu, 11 Dec 2008 05:28:06 -0800, PMBO
wrote: Ron - I'm using a simple datedif =DATEDIF(A1,A2,"d") This should work: =SUMPRODUCT(--(((MONTH(ROW(INDIRECT(StartDt&":"&EndDt)))<2)+ (DAY(ROW(INDIRECT(StartDt&":"&EndDt)))<29))0)) --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
Fantastic - It works perfectly. Thank You Ron.
"Ron Rosenfeld" wrote: On Thu, 11 Dec 2008 05:28:06 -0800, PMBO wrote: Ron - I'm using a simple datedif =DATEDIF(A1,A2,"d") This should work: =SUMPRODUCT(--(((MONTH(ROW(INDIRECT(StartDt&":"&EndDt)))<2)+ (DAY(ROW(INDIRECT(StartDt&":"&EndDt)))<29))0)) --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excluding leap days in a formula
On Thu, 11 Dec 2008 13:10:12 -0500, Ron Rosenfeld
wrote: On Thu, 11 Dec 2008 05:28:06 -0800, PMBO wrote: Ron - I'm using a simple datedif =DATEDIF(A1,A2,"d") This should work: =SUMPRODUCT(--(((MONTH(ROW(INDIRECT(StartDt&":"&EndDt)))<2)+ (DAY(ROW(INDIRECT(StartDt&":"&EndDt)))<29))0) ) --ron A quick note -- the above formula returns an inclusive count. You should probably subtract one (1) to obtain the result you really want. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for adding days to a date excluding weekends and holidays? | Excel Discussion (Misc queries) | |||
excel total days formula including weekends, excluding holidays | Excel Discussion (Misc queries) | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
Working Days excluding Sundays | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) |