Count number of days from dates

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 2016
Hi,

I have a pivot table with changes on the use of a slicer. I need to have a count of the number of days in the list. In the example, the count should be 14. I have tried adding a new calculated field but cant get the answer i need, 14. Any help appreciated.

Row Labels
03/08/2021
04/08/2021
05/08/2021
09/08/2021
11/08/2021
12/08/2021
13/08/2021
18/08/2021
19/08/2021
20/08/2021
23/08/2021
25/08/2021
27/08/2021
31/08/2021
Grand Total
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Excel store dates in numbers, can you try something like this?

Book1
ABC
1Row Labels
28/3/202114
38/4/2021
48/5/2021
58/9/2021
68/11/2021
78/12/2021
88/13/2021
98/18/2021
108/19/2021
118/20/2021
128/23/2021
138/25/2021
148/27/2021
158/31/2021
16Grand Total
Sheet1
Cell Formulas
RangeFormula
C2C2=COUNT(A:A)
 
Upvote 0
Hi, I may have not been clear enough, apologies. The table shown is actually a pivot table and data changes depending on the slicer selection. Using =count(A:A) doesn't work, unless I am doing something wrong.
 
Upvote 0
If I select A:A i get an error. I have tried to add a calculated field bu no options give the correct answer. The answer in the pivot tells me how many records of each date there are, no a count of the unique dates. For example, it will show, 08/03/2021 - 3 meaning the date has 3 records. I do not want to count number of records, just unique dates, so the list above shows 14 unique dates, that is the answer I need. Really struggling, im sure its simple.
 
Upvote 0
Hi, I may have not been clear enough, apologies. The table shown is actually a pivot table and data changes depending on the slicer selection. Using =count(A:A) doesn't work, unless I am doing something wrong.
is should work, is your dates in Column A?
 
Upvote 0
Hi, no they are actually in CW in the real file but I replaced A with CW in the formula.
Screenshot 2021-11-29 195131.jpg
 
Upvote 0
so, what is the error with the COUNT() function?
 
Upvote 0
Hi, the error I get is a circular reference. If I then press ok the result is 0
 

Attachments

  • Screenshot 2021-11-30 082329.jpg
    Screenshot 2021-11-30 082329.jpg
    106.6 KB · Views: 6
Upvote 0
Hi, the error I get is a circular reference. If I then press ok the result is 0
you need to move the cell with the COUNT() function away from Column CW, that's what caused the circular error.
 
Upvote 0
Ok I have now added another column, calculated field which takes the sum of the dates in the CW column. (see image). I now get a number, currently 19 but this is not the correct answer? As the pivot table changes due to the slicer selections, this number should change to reflect the count of the ones, but it doesn't. It does not change and stays on 19?
 

Attachments

  • Screenshot 2021-11-30 092856.jpg
    Screenshot 2021-11-30 092856.jpg
    53.1 KB · Views: 6
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top