month formulas

[Guest]

I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

 

[Guest]

Ok, I messed with it some more, and have it half way working...

I made the formula:

=COUNTIF(B2:B299, ">"&I21) where I21 is the cell storing the month name, the
date I worked being in the range B2:B299. Now, the problem is, even if I go
past the month of June for example, it still counts it because I don't know
how to stop it.

Say July is stored in cell I22, I need something like =COUNTIF(B2:B299,
">"&I21&<I22) but that will of course not work.

 

[Guest]

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

 

[Guest]

it worked for me =COUNTIF(B2:B299, I21).What you are counting tho has to look
like whatever is in I21 so if you are counting dates say for june and you
have june dates in your column,a date of 1/6/2006 formated say mmm so it
shows up as June in I21,that wont count 2/6/2006,or 4/6/2006,it will only
count 1/6/2006
paul
(e-mail address removed)
remove nospam for email addy!

 

[Guest]

Thanks for that! Finally solved many days of worthless work.

And now I have a harder one...

Is there a formula that I could use that would gather the column and cell
numbers for cells containing the months....that didn't make sense,

For instance,

B2:B299 is where I enter the date of which I work. There is no way to tell
how many days I will work in a month. The date being in the d-mmm format
stores part of the month, which I know Excel stores as a number anyway...so
how can I make a formula that would count how many days I worked in that
month...and then somehow take data that is in another coresponding column and
devide???

Like say column D held a different number, the devisor...so it would be like
B22/D22, only I need the totals, like How many days I worked, devided by the
total of all the numbers in column D

Make any sense??

 

[Guest]

Wouldn't just be the formula I have given divide by your cell?

=Sumproduct(month(b2:b299)=6)/Divisor

The SUMPRODUCT gives count of days worked in June ... but I don't fully
understand the divisor. You give an example of B22/D22 but B22 is a date(?)
so you cannot divide it.

Perhaps an example of the data would help me!

 

[Bob Phillips]

Don't forget the coercer

=SUMPRODUCT(--(MONTH(B2:B29)=6))/Divisor

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Wouldn't just be the formula I have given divide by your cell?

=Sumproduct(month(b2:b299)=6)/Divisor

The SUMPRODUCT gives count of days worked in June ... but I don't fully
understand the divisor. You give an example of B22/D22 but B22 is a date(?)
so you cannot divide it.

Perhaps an example of the data would help me!

Please Help...

 

[Roger Govier]

Hi

I wonder whether what the OP is looking for is

=SUMPRODUCT(--(MONTH(B2:B299)=6),D2229)/SUMPRODUCT(--(MONTH(A1:A20)=6))
This would give the average value per day for days worked in June.

 

[Guest]

YES! This worked perfectly, and I was able to modify that formula to take
care of 4 other things I needed.

Much Thanks To Everyone!