Formula ?

  • Thread starter Thread starter TQ
  • Start date Start date
T

TQ

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
 
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.
 
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 !
 
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
 
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'
Click to expand...

can I get the days which follow the period I provided ?

Thanks a lot !
 
Back
Top