Dynamic Formatting of Range returned by Filter Function

[Bedlam]

Hi
I have filter functions in place that may return between 1-2000 rows of data for about 90 columns. One of the irritating things is it returns dates simply as numbers, also blanks are returned as zeroes. Sometimes there are blank dates too. I would like to set formats say for the first row and these to be dynamically extrapolated for subsequent rows. I don't want a macro answer as these are being generated in too many files with the same layout for me to have to go in and run macros. (Also they are being generated by an external piece of software - Alteryx Designer). So my questions are -
Is it possible?
How do I do it without using VBA?

Regards
Mark

 

[Fluff]

Why not just format the entire column as a date?

 

[Bedlam]

Why not just format the entire column as a date?

I have done that at present but was getting hit with Excel memory errors. So I manually set formatting for all relevant areas to about 2k rows.

 

[Fluff]

Are you changing the cell format, or using conditional formatting?
As I would not expect any problems simply by changing the cell format for an entire column.

 

[Bedlam]

Hi

I used both. I set conditional formatting for zero value dates to another text colour. I set date fields to various date formats.

 

[Fluff]

What is the formula you are currently using to get the data?

 

[Bedlam]

Hi
I think that is where I need to adjust =FILTER('All Cases'!A2:CG1048576,('All Cases'!BS2:BS1048576=Dates!B3))
ie replace the 1048576 with something more reasonable like 2000.

Regards

mark

 

[Peter_SSs]

I set conditional formatting for zero value dates to another text colour.

Instead of Conditional Formatting for the date columns try a custom format like this

dd-mmm-yyyy hh:mm;;

 

[Fluff]

You can get rid of the 0s instead of blanks like

=LET(f,FILTER('All Cases'!A2:CG1048576,('All Cases'!BS2:BS1048576=Dates!B3)),IF(f="","",f))

 

[Bedlam]

Thanks @Fluff for your help. Also @Peter_SSs for what I know is a potential solution