comparing fields which are the same post formatting but which are different preformatting

Hi

I have a merged worksheet for which I want to compare date values stored in different formats

In one column the data is a number eg 20091202 is 2 Dec 2009. In the field with which I want to make the comparison,the date is actually stored as a date format yyyymmdd. This means that the display looks the same but fields can not be compared as the date format corresponds to an underlying serial number which is different.

I'm sure this is easy I'm not that familiar with excel and in truth, it shows!

Fundamentally I would like the values I see on the screen to be compared directly as they appear after formatting but the underlying pre formatted values are not the same and do not vary proportionately. In the case of the 'number' format, 20141130 is followed by 20141201, ie not a proportionate serial increase equivalent to 1 day

Any thoughts will be much appreciated.

ps If this has previously been dealt with elsewhere, my apologies, but if you would post the link it would be appreciated

Hi

Try This. If the date number (e.g. 20141130) is in cell A1, then:-

Please Login or Register  to view this content.

Then format that cell as a date in whichever form you'd like.

or if you just want to compare the dates (say column A and column C) then:-

Please Login or Register  to view this content.

Excellent!
Works a treat. Thanks a lot Beamernsw!

You're very welcome. Glad to help.