Problem with Grouping Dates in a Pivot table

Hello

With help from this forum I am using a formula below to populate a column with dates which are recognised as dates in a pivot table...

=DATE(VALUE(MID(AN40,C40+1,4)),VALUE(LEFT(AN40,B40-1)),VALUE(MID(AN40,B40+1,C40-B40-1)))

This works but unfortunately when I try to Group the data in the Pivot table by month/year it cannot group the data... maybe because there are some blank cells ?

So I tried adding a 'filler' date in the year 1900 that I could then just filter out in the pivot table but it doesn't work .... maybe because the filler date is not formatted as a date?

=IFERROR(DATE(VALUE(MID(AN40,C40+1,4)),VALUE(LEFT(AN40,B40-1)),VALUE(MID(AN40,B40+1,C40-B40-1))),"01/01/1900")

I would appreciate help on how to fill the blanks in such a way that the Pivot table field can be Grouped by Year/Month etc.. (Ideally I wouldn't use a 'filler' date but my understanding is that blank cells prevent the pivot table field being Grouped)

Thank you

the double quotes around anything tell excel to read it as text. So replace "01/01/1900" by Date(1900,1,1) to make the grouping work.
I think even putting a plain 0 will suffice for grouping for excel dates are nothing more than formatted numbers.

Thank you Roel - this worked fine