CHOOSECOLS(FILTER... giving '00/01/1900' for date source columns

[ollyhughes1982]

Hi,

I have the following formula in the cell A3 of the ‘Lapsed Memberships’ worksheet:

=CHOOSECOLS(FILTER('Club Membership'!$A$3:$AA$1102,('Club Membership'!$AA$3:$AA$1102="Lapsed")*('Club Membership'!$AA$3:$AA$1102<>"Deceased")),1,2,3,4,6,7,8,9,15,16,20)

It works for some of the columns, but the following ones just have blanks (with errors). - All are date columns

• Date of Birth (Column D)
• Latest Application Date (Column I)
• Latest Grant Date (Column J)

I have tried changing the column formats, without success.

I’ve redacted the other data.

Any help appreciated!

Thanks in advance,

Olly.

 

[ollyhughes1982]

Apologies, my bad, it isn't replacing values with '00/01/1900', but the empty date fields with it. is there a way to show these empty date fields as empty?

Apologies, I hadn't realised these were records where all of these date cells were empty, so just assumed they all weren't working. When there is a date present, it does work.

Thanks.

 

[ollyhughes1982]

I have now resolved this. I set the destination cell formats to dd/mm/yyyy;; and they then displayed an empty cell.

Thanks.