If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula ?
Hi, anyone who know the solution below ?
Start Date End Date 01.01.10~31.12.10 01.01,11~31.12.11 1)01/10/09 31/01/10 days ? days ? 2)01/03/10 31/04/10 days ? days ? 3)01/12/10 31/01/11 days ? days ? Is anyone know how to get the days which fall between the stimulate period follow the info given ? Thanks |
#2
|
|||
|
|||
Formula ?
If the dates are entered in the normal mm/dd/yy format you simply subtract
the cell with the latest date from the cell with the earlier date. i.e. =C12-B12 if the dates are in cells B12 and C12. If you are entering the formula in D12 you willneed to format from a date format to a number format. "TQ" wrote: Hi, anyone who know the solution below ? Start Date End Date 01.01.10~31.12.10 01.01,11~31.12.11 1)01/10/09 31/01/10 days ? days ? 2)01/03/10 31/04/10 days ? days ? 3)01/12/10 31/01/11 days ? days ? Is anyone know how to get the days which fall between the stimulate period follow the info given ? Thanks |
#3
|
|||
|
|||
Formula ?
Hi Rick Cl No,what I mean is, I want the answer (how many days) which only fall in the period which I meantion. Example, date start : 01/11/09 & end date : 3/1/11. I want excel get the answer for how many days of this period in year 2010 (answer=365 days) & how many days in year 2011(answer=3 days). Answer differential by year although the period provide overlap. Thanks ! "Rick Cl." wrote: If the dates are entered in the normal mm/dd/yy format you simply subtract the cell with the latest date from the cell with the earlier date. i.e. =C12-B12 if the dates are in cells B12 and C12. If you are entering the formula in D12 you willneed to format from a date format to a number format. "TQ" wrote: Hi, anyone who know the solution below ? Start Date End Date 01.01.10~31.12.10 01.01,11~31.12.11 1)01/10/09 31/01/10 days ? days ? 2)01/03/10 31/04/10 days ? days ? 3)01/12/10 31/01/11 days ? days ? Is anyone know how to get the days which fall between the stimulate period follow the info given ? Thanks |
#4
|
|||
|
|||
Formula ?
I've set up a table as follows:
Row 1 are headers: A1 'Start', B1 'End', C1 '2007' (formatted as General), D1 '2008', E1 '2009' etc Columns A and B from row 2 down are formatted as Date. Then the formula in C2 is: =IF(OR(YEAR($A2)C$1,YEAR($B2)C$1),0,IF(AND(YEAR( $B2)=YEAR($A2),YEAR($B2)=C$1),$B2-$A2+1,IF(YEAR($B2)=C$1,$B2-DATE(C$1-1,12,31),DATE(C$1,12,31)-$A2+1))) Drag fill this formula to the rest of your table and it will fill in the number of days per year under that year's header. Note, however, you might want to adjust the use of the '+1' in the formula. Some people like to think the number of days between say 1/3/10 and 30/4/10 is 31 + 30 i.e. 31 days of March plus the 30 days of April, the '+1's in the formula account for that. However, if you calculate 30/4/10-1/3/10 in Excel the answer it gives is 60 days, not 61. So, if your formulas need to link to the way Excel typically calculates number of days between 2 dates then you need to think about those '+1's and whether to amend them. Regards, Tom "TQ" wrote: Hi Rick Cl No,what I mean is, I want the answer (how many days) which only fall in the period which I meantion. Example, date start : 01/11/09 & end date : 3/1/11. I want excel get the answer for how many days of this period in year 2010 (answer=365 days) & how many days in year 2011(answer=3 days). Answer differential by year although the period provide overlap. Thanks ! "Rick Cl." wrote: If the dates are entered in the normal mm/dd/yy format you simply subtract the cell with the latest date from the cell with the earlier date. i.e. =C12-B12 if the dates are in cells B12 and C12. If you are entering the formula in D12 you willneed to format from a date format to a number format. "TQ" wrote: Hi, anyone who know the solution below ? Start Date End Date 01.01.10~31.12.10 01.01,11~31.12.11 1)01/10/09 31/01/10 days ? days ? 2)01/03/10 31/04/10 days ? days ? 3)01/12/10 31/01/11 days ? days ? Is anyone know how to get the days which fall between the stimulate period follow the info given ? Thanks |
#5
|
|||
|
|||
Formula ?
Thanks Tom-S, Actually I am giving a simple example. In my real case, the "year" for compate not so easy as 2007 or 2008, I have to follow accounting period that is between April to March as comparison, can i still apply this fomula ? Example as below Row 1 are headers: A1 'Start', B1 'End', C1 '01.04.07~31.03.08' , D1 '01.04.08~31.03.12', E1 ' 31.03.12' can I get the days which follow the period I provided ? Thanks a lot ! "Tom-S" wrote: I've set up a table as follows: Row 1 are headers: A1 'Start', B1 'End', C1 '2007' (formatted as General), D1 '2008', E1 '2009' etc Columns A and B from row 2 down are formatted as Date. Then the formula in C2 is: =IF(OR(YEAR($A2)C$1,YEAR($B2)C$1),0,IF(AND(YEAR( $B2)=YEAR($A2),YEAR($B2)=C$1),$B2-$A2+1,IF(YEAR($B2)=C$1,$B2-DATE(C$1-1,12,31),DATE(C$1,12,31)-$A2+1))) Drag fill this formula to the rest of your table and it will fill in the number of days per year under that year's header. Note, however, you might want to adjust the use of the '+1' in the formula. Some people like to think the number of days between say 1/3/10 and 30/4/10 is 31 + 30 i.e. 31 days of March plus the 30 days of April, the '+1's in the formula account for that. However, if you calculate 30/4/10-1/3/10 in Excel the answer it gives is 60 days, not 61. So, if your formulas need to link to the way Excel typically calculates number of days between 2 dates then you need to think about those '+1's and whether to amend them. Regards, Tom "TQ" wrote: Hi Rick Cl No,what I mean is, I want the answer (how many days) which only fall in the period which I meantion. Example, date start : 01/11/09 & end date : 3/1/11. I want excel get the answer for how many days of this period in year 2010 (answer=365 days) & how many days in year 2011(answer=3 days). Answer differential by year although the period provide overlap. Thanks ! "Rick Cl." wrote: If the dates are entered in the normal mm/dd/yy format you simply subtract the cell with the latest date from the cell with the earlier date. i.e. =C12-B12 if the dates are in cells B12 and C12. If you are entering the formula in D12 you willneed to format from a date format to a number format. "TQ" wrote: Hi, anyone who know the solution below ? Start Date End Date 01.01.10~31.12.10 01.01,11~31.12.11 1)01/10/09 31/01/10 days ? days ? 2)01/03/10 31/04/10 days ? days ? 3)01/12/10 31/01/11 days ? days ? Is anyone know how to get the days which fall between the stimulate period follow the info given ? Thanks |
Thread Tools | |
Display Modes | |
|
|