COUNTIF formula to return dates within week

[Dawber]

I have an Excel spreadsheet with a series of dates in one column. I want to use a formula to count the number of entries in any particular week.

eg. =COUNTIF(A:A,week 33)

 

[Hasit]

Whats the format of your dates? Are they split up by any means? e.g. week, month etc?

 

[AnotherHiggins]

On what day of the week do you start your weeks? Do you use Sunday - Saturday? Monday - Sunday? Something else?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Dawber]

The format of the date is DD/MM/YYYY.

The week is a working week, starting on Monday and ending on Friday.

Thanks for taking the time to respond.

 

[PeterMoran]

Hi,

Unfortunately according to Excel Guru Chip Pearson Excel does not have a function for converting week numbers into dates. However you can easily setup a table with dates in col A and the following formula in col B:

=WEEKNUM(A2,2)

This will give you Week numbers starting on Monday, but check that Excel treats the first week of the year the same as you want.

You are then able to select the start and end dates for the week, and the following will do what you want I think.

Set up two cells as StartDate and EndDate:

=COUNT(A:A)-COUNTIF(A:A,"<"&StartDate)-COUNTIF(A:A,">"&EndDate)

Good Luck!

Peter Moran