Wildcards with COUNTIF on Date Cells

[sejones]

Hello All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am trying to count the number of time the 15<SUP>th</SUP> and the 28<SUP>th</SUP> of the month show up in Column A. Column A is in date format but not all cells have a date. In this example I am only looking for the 15<SUP>th</SUP> but in the end I want the combined count of both.<o></o>
<o> </o>
This formula gives me a count of 1<o></o>
=(COUNTIF(TimeSheets!A1:A119,"3/15/2012"))<o></o>
These return 0<o></o>
=(COUNTIF(TimeSheets!A1:A119,"*15*"))<o></o>
=(COUNTIF(TimeSheets!A1:A119,"*/15/*"))<o></o>
Or any other combination of wildcards with * or ?<o></o>
<o> </o>
Maybe there is something other than COUNTIF I can use.<o></o>
<o> </o>
<TABLE style="MARGIN: auto auto auto -1.15pt; WIDTH: 115pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=153><TBODY><TR style="HEIGHT: 21pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #d9d9d9; HEIGHT: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=153 noWrap>

Date<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Thursday, March 01, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Friday, March 02, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #ddd9c4; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: 1.0pt; mso-border-bottom-alt: .5pt; mso-border-right-alt: 1.0pt; mso-border-top-alt: .5pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" width=153 noWrap>

Saturday, March 03, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #ddd9c4; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: 1.0pt; mso-border-bottom-alt: .5pt; mso-border-right-alt: 1.0pt; mso-border-top-alt: .5pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" width=153 noWrap>

Sunday, March 04, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Monday, March 05, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #fcd5b4; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: 1.0pt; mso-border-bottom-alt: .5pt; mso-border-right-alt: 1.0pt; mso-border-top-alt: .5pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" width=153 noWrap>

Tuesday, March 06, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Wednesday, March 07, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Thursday, March 08, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Friday, March 09, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 10"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #ddd9c4; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: 1.0pt; mso-border-bottom-alt: .5pt; mso-border-right-alt: 1.0pt; mso-border-top-alt: .5pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" width=153 noWrap>

Saturday, March 10, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 11"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #ddd9c4; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: 1.0pt; mso-border-bottom-alt: .5pt; mso-border-right-alt: 1.0pt; mso-border-top-alt: .5pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" width=153 noWrap>

Sunday, March 11, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 12"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Monday, March 12, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 13"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Tuesday, March 13, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 14"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Wednesday, March 14, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 15"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Thursday, March 15, 2012<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 16"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=153 noWrap>

<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 17"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #d9d9d9; HEIGHT: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-alt: solid windowtext 1.0pt" width=153 noWrap>

Total Hours Worked<o></o>​

</TD></TR><TR style="HEIGHT: 21pt; mso-yfti-irow: 18; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 115pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #d9d9d9; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt" width=153 noWrap>

Hours In This Pay Period<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
<o></o>
<o>Thanks,</o>
<o>Steve</o>

 

[Jonmo1]

You only want the Day, regardless of month and year?

Try

=SUMPRODUCT(--(DAY(TimeSheets!A1:A119)=15))

For multiple days..

=SUMPRODUCT(--(DAY(TimeSheets!A1:A119)={15,28}))

Hope that helps.

 

[jaybee3]

Array formula version:
{=SUM(IF((DAY(TimeSheets!A1:A119)=15)+(DAY(TimeSheets!A1:A119)=28),1,0))}

Will count anything starting at 15th or 28th. Entered with ctrl+shift+enter instead of just enter.

 

[sejones]

You only want the Day, regardless of month and year?

Try

=SUMPRODUCT(--(DAY(TimeSheets!A1:A119)=15))

For multiple days..

=SUMPRODUCT(--(DAY(TimeSheets!A1:A119)={15,28}))

Hope that helps.

Yes, I do not care about the month or year only the 15th and 28th.


Your formula returns an error. . .
A value used in the formula is of the wrong data type.


Thanks

 

[barry houdini]

Is there text in the range? If so try this version

=SUMPRODUCT(--(TEXT(TimeSheets!A1:A119,"d")={"15","28"}))

 

[Jonmo1]

Sounds like your dates are not really dates, just text strings..

What do these return

=ISNUMBER(TimeSheets!A1) and filled down all the way to A119

Are they all True or False, or mix of both?

 

[sejones]

Thanks Everyone !

This one worked from Barry. . .


=SUMPRODUCT(--(TEXT(TimeSheets!A1:A119,"d")={"15","28"}))


Steve

 

[sejones]

Is there text in the range? If so try this version

=SUMPRODUCT(--(TEXT(TimeSheets!A1:A119,"d")={"15","28"}))

Okay Barry, since you had the winning formula Is there a way to count the number of remaining 15th's and 28th's for the year from a certain date?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Example, if Column A's last date is March 15th, how many more 15 and 28 would there be, not including March 15th?<o></o>
<o> </o>

If I should start and new thread let me know.


<o></o>
Thanks,
Steve

 

[jaybee3]

=(12-TEXT(A1,"mm"))*2+IF(--(TEXT(A1,"d"))<25,1,0)+IF(--(TEXT(A1,"d"))<18,1,0)

Change A1 to the last cell.

 

[sejones]

=(12-TEXT(A1,"mm"))*2+IF(--(TEXT(A1,"d"))<25,1,0)+IF(--(TEXT(A1,"d"))<18,1,0)

Change A1 to the last cell.

Thanks,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
This seems to give me the total for one calendar year. What I am looking for is from current date to the end of the year or from the last date in column A to the end of the current year.<o></o>
<o> </o>
Any other ideas?<o></o>
<o> </o>
<o> </o>
<o> </o>
Steve<o></o>