|
DOnt calculate leap year
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 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 29 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 |
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) "kckar" wrote in message ... 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 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 29 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 |
Please don't post to more than one group in separate postings.
-- Kind regards, Niek Otten Microsoft MVP - Excel "kckar" wrote in message ... 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 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 29 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 |
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) ? |
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 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 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 29 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 -- Dave Peterson |
i need february 29th to be factored out so that t would be 9+356=365 not just automatically making 9+357=365 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 |
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)))))) )) -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Peterson wrote: 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 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 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 29 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 |
Somebody used the row(indirect()) just a couple of days ago.
I was lucky that I remembered! Dick Kusleika wrote: 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)))))) )) -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Peterson wrote: 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 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 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 29 -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 -- Dave Peterson |
thank you sorry for the trouble it works perfectly as far i can see right now kacey -- kckar ------------------------------------------------------------------------ kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322 View this thread: http://www.excelforum.com/showthread...hreadid=387998 |
| All times are GMT +1. The time now is 05:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com