| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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) ? |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do we calculate T-bill Price in 365 days year. | Excel Worksheet Functions | |||
| How to compare current year to prior year in bar chart? | Charts and Charting in Excel | |||
| leap year question | Excel Discussion (Misc queries) | |||
| how do i calculate principal payed in one year in excel | New Users to Excel | |||
| calculate negative or positve difference in time | Excel Discussion (Misc queries) | |||
Linear Mode
