Need help in creating a formula in Excel! 1

[Shaz19]

I have a fomula which can add the days from say 01 Oct 02 - 28 Mar 03---=IF(ISBLANK(A18),"0",(B18-A18)+1)---=179 Days. What I need the formula to do is know how many days are in a month (whichever month it mybe) and add up how many days, in a particular month, were used.
For example:
I have dates from 01 Oct 02-28 Mar 03, 20 Oct 02-19 Feb 03, and 28 Oct 02-5 Jan 03. I want the formula to tell me in the month of Oct how many days were used (47 days). In the month of Nov how many days were used (90 days). And so on and so forth.

Does anyone have any ideas?
Thank you in advance for your help.

 

[PeterMoran]

Hi Shaz19,

I assume you have a column of dates commencing at A4.

In the column to the right (B) enter the formula =MONTH(A4) and copy it down beside all the dates. This will give the month number of each date in col B.

Then below the month numbers in col B enter the formula =COUNTIF(B4:B999,10) - this will count the no of October dates.

HTH

Good Luck!

Peter Moran
Two heads are always better than one!

 

[SkipVought]

Shaz19,

Do you understand that a date in Excel is an integer number?

Today's data number (also called date serial) is 37709.

So, if 01 Oct 02 were in A1 and 28 Mar 03 were in A2, then if A3 had the formula =A2-A1, the value would be 178.

It's as simple as that!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DeLaMartre]

Peter,

I think your solution is simply brilliant. Thank you! -Bob in California

 

[TonyJollans]

Hi Shaz19,

Maybe I'm not understanding something but the answers you have so far don't seem to solve your problem so I'll chip in with my offer.

If you are happy to use quite a few columns, then this should do what you want:

A1: 01 Oct 02 (your start date)
B1: 28 Mar 03 (your end date)

C1: =MAX("1 Oct 2002",A1)
D1: =MIN(EOMONTH("Oct 2002",0),B1)
E1: =IF(D1>=C1,D1-C1+1,0)

F1: =MAX("1 Nov 2002",A1)
G1: =MIN(EOMONTH("Nov 2002",0),B1)
H1: =IF(G1>=F1,G1-F1+1,0)

Continue across further columns for all the months you are interested in, and repeat for each date range in each row. Finally just sum the figures in columns E,H,K, etc.

If you don't have EOMONTH, Install the Analysis ToolPak (Tools > AddIns).

I hope that makes sense!

Enjoy,
Tony