Pivot Table Glitch with Distinct Grand Total on Dates?

I'm using Excel 2013.
I have a pivot table with a client_id row and distinct counts of visit_dates for each client. The Grand Total of the distinct counts gives the grand total of the overall distinct dates rather than the sum of all the distinct totals for each client.
For example:
Client_1 has a count of 3 distinct dates.
Client_2 has a count of 2 distinct dates..
Client_3 has a count of 2 distinct dates.

If the same date is shared by more than one client Excel counts it as 1 instead of 2. For instance: both Client_1 and Client_2 have the date 4/1/2017. The grand total of the pivot will be 6 when it should be 7.

I don't see that outcome when counting other formats besides dates. That column is actually formatted as general but the values are in format 99/99/9999.

Is this a known glitch?

Please let me know if I need to provide more info or make the problem clearer. It's not that big of a deal since I can delete the generated grand total and total the counts.

Thanks.

Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I attached a workbook with 4 sheets - Before,After(with incorrect grand total), Desired, and an extra.

Thank you.

Your Dates are as text not as Date format
(time also)

Thanks, oeldere.
I understand most of your fix. I'm still trying to figure out "select column 2 in filters and filter on 1." I'll get that after spending time on it.

Is the Excel pivot supposed to generate the grand total of the distinct count of dates I was getting? Is that a bug or is that the correct outcome?

Yes, I see that the format is text or general.

Yes, I want non duplicate values. If an item has the same date listed twice, it should be counted as 1.

OK, I thought distinct would count unique values.

Thanks to both of you for your help.

Here is solution with PowerQuery (no additional columns)

I'll check out PowerQuery. I've never used it but shouldn't have any trouble with it. I write a lot of SQL queries.

Good luck with PowerQuery (very usefull tool)

thanks for rep.

I've already marked the thread as answered and it is answered. I want to add some clarification. I tried another pivot but with a distinct count of data in another format besides mm/dd/yyyy. I get the same results. I had thought I had gotten different results in the past with the grand total of distinct counts but must have been mistaken. When I asked the question, I had thought that Excel was doing something different with dates (even though that date column was not formatted as date). Apparently that is the usual result with the pivots.

It's resolved now. Thanks again.