A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula ?



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 05:55 PM posted to microsoft.public.excel.misc
TQ
external usenet poster
 
Posts: 62
Default 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  
Old June 3rd, 2010, 07:03 PM posted to microsoft.public.excel.misc
Rick Cl.
external usenet poster
 
Posts: 4
Default 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  
Old June 3rd, 2010, 10:31 PM posted to microsoft.public.excel.misc
TQ
external usenet poster
 
Posts: 62
Default 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  
Old June 4th, 2010, 10:36 AM posted to microsoft.public.excel.misc
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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  
Old June 6th, 2010, 04:30 PM posted to microsoft.public.excel.misc
TQ
external usenet poster
 
Posts: 62
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.