Can you automatically format dates correctly

[rowee21]

This might seem like a strange question, I am aware you can format an entire column, or multiple columns as dates with just a few clicks. However, when dealing with a lot of reports containing over 100 columns, where the dates need to be correctly formatted. It's a laborious task to go through and manually do this, and very easy to miss the odd column.

So my question is this:
Is there a way I can get Excel to go through the report looking for anything that is a date (or datetime) and set the formatting to 'Short Date' for example?

Thanks

 

[offthelip]

you can use the Replace function in Excel to search a sheet for a format and change the format. However you will have to search for all the different formats which your sheet starts with, however this will guarantee all dates which you search for are changed to the format you want. Type control H and select the format drop down on the right hand side