Excel questions - calculated dates and pivot tables

Factual Questions
1

I have a table in Excel which contains the following columns:
**PQR End **- this is formatted as a date
PQR Freq - this is formatted as a number, representing the frequency in number of years
PQR Due - this is a formula to add the frequency to the PQR End field, to calculate the next date that PQR is due. If there is no PQR End date value, this should be blank. This column is formatted as a date.

Question 1: Is this the right formula to use to calculate PQR Due? =IF(ISBLANK(F2),"",DATE(YEAR(F2)+(H2),MONTH(F2),DAY(F2)))

It appears to be working as required, but I’m then having problems when I go to my pivot table which includes these two date fields.

The PQR End field in the pivot table allows me to sort oldest to newest, and offers those handy Date Filters (last week, this week, etc).

The PQR Due field in the pivot table doesn’t give me these same options, which indicates to me that it’s seeing the content as text rather than dates. So I get A-Z sort options, and Label Filters instead of Date Filters.

Question 2: How can I get the pivot table to recognise PQR as dates instead of text?

Difficulty: Dates need to cope with both the US and UK format.

2

I’m spit-balling here, but I bet part of the problem is your IF() using “” to mean an empty cell. That looks like an empty cell, but internally it’s a cell full of text. The text being a zero-length string.

Lots and lots of amateur problems arise because of the difference between what cells look like and what they are. Not that you’re confused, but consider all the goofy Excel Qs that arise because so many users don’t understand that a date is really a sequential number (39456 or whatever) just formatted to look like “mm/dd/yyyy” or “dd/mm/yyyy”.

As a first step, change your due date formula to return 0 if the frequency is empty. See if that convinces the pivot table to recognize the fields as dates. If so, then apply formatting to those cells to display “mm/dd/yyyy” or “dd/mm/yyyy” only for non-zero values and to display “” for zero values.

3

Thank you, LSLGuy. It wasn’t quite that, but your reply reminded me that I had applied some conditional formatting to hide the (blank) values. That was obviously being read as text!

4

Probably a matter of personal preference but I’d consider rather than:
=IF(ISBLANK(F2),"",DATE(YEAR(F2)+(H2),MONTH(F2),DAY(F2)))

using:
=edate(F2,H2*12)
It’s a simpler construction and if either F2 or H2 are zero your pivot table will handle it as a date.