Formula ?
in [General]
|
Prev: Error message: "Too many cell formats"
Next: Does anyone have a copy of a Payroll Chart when paid every 2 week
From: TQ on 3 Jun 2010 12:55 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
From: Rick Cl. on 3 Jun 2010 14:03 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
From: TQ on 3 Jun 2010 17:31 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
From: Tom-S on 4 Jun 2010 05:36 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
From: TQ on 6 Jun 2010 11:30
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 |