Counting dates in a list

L33

Board Regular
Joined
Jul 2, 2008
Messages
104
Hi,
I found this handy formula some months ago on these messageboards:

=countif(Sheet1!B:B,">=01/03/08")-countif(Sheet1!B:B,">31/03/08")

It counts number of dates in a given list of dates within the specified period.

But I now want to replace the underlined sections with cell references - but whatever I try, Excel won't let me do that.

Basically, I want put dates in to two cells, use Networkdays to get working days, then deduct the number of days in a pre-defined list between those two dates aswell.

I know there's a holidays parameter on the Networkdays function, but I need to display this to users in a step by step fashion.


Any help much appreciated.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
">="&cellref

encase operator in quotations and & to cell reference.

hope that helps.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try a formula like

=COUNTIF(Sheet1!B:B,">="&A1)-COUNTIF(Sheet1!B:B,">"&A2)

where A1 and A2 house dates.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
For example...

=COUNTIF(Sheet1:B:B,">="&F2)-COUNTIF(Sheet1!B:B,">"&G2)

where F2 <= G2.
 

L33

Board Regular
Joined
Jul 2, 2008
Messages
104

ADVERTISEMENT

Genius! Many thanks.


Just have to work out why my Networkdays is suddenly returning the "#NAME?" error when it's been working fine until now.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Is the Analysis Toolpack ticked in Tools > Add-ins?
 

L33

Board Regular
Joined
Jul 2, 2008
Messages
104
It was, but I've just unticked it, closed down Excel, then opened it up again and ticked it again - it appears to be working ok now. Must have been a small bug.

Many thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,334
Messages
5,595,567
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top