Count the number of dates between a date range

Hello

Attached is the sample worksheet. On the Charts sheet, i am trying to create a formula (in cell D25) which count the number of dates in column B on the Unsourced List 2015 sheet which is inbetween the two dates in A25 and B25 on the Charts Sheet

The formula i am currently using is below and just returns 0 for Q1, although it should be 73

Please Login or Register  to view this content.

Thank You

SAMPLE.xls

All but one of your dates in column B are text values. To change them you can highlight the data in column B and then click on Data | Text-to-Columns, then click Fixed Width | Next | Next and on the third panel click against Date with DMY selected and click Finish.

You can also simplify your formula in D25 to this:

=COUNTIFS('Unsourced List 2015'!B:B,">="&A25,'Unsourced List 2015'!B:B,"<="&B25)

as SUMPRODUCT doesn't work very efficiently with full-column references (you also had a misplaced bracket).

Hope this helps.

Pete

surely if this was the case, it would return 1 instead of 0?

The dates on the unsourced list have a ' attached at the front which is why it returns false. Due to the data which i retrieve has this inputted already on it, i can't change it unless i can place something in a macro to remove the '

Your SP formula should be:

=SUMPRODUCT(('Unsourced List 2015'!B:B>=Charts!A25)*('Unsourced List 2015'!B:B<=Charts!B25))

Note the bracket at the end and one removed from the middle. Better to use COUNTIFS, though, as in my previous post. For either of these formulae, you need to have proper dates in column B, and my previous post tells you how you can convert them quite easily.

Hope this helps.

Pete

Actually, your formula has a slight syntax issue..a Misplaced )

=SUMPRODUCT(('Unsourced List 2015'!B:B>=Charts!A25))*('Unsourced List 2015'!B:B<=Charts!B25)
should be
=SUMPRODUCT(('Unsourced List 2015'!B:B>=Charts!A25)*('Unsourced List 2015'!B:B<=Charts!B25))

This corrected version indeed does return 1 without modifying any dates on the Unsourced list sheet..

Thank you for your help!