ExcelBanter - View Single Post

dpb

On 5/7/2018 3:52 AM, wrote:
And an explanation for my problem:

The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion.

When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export.

If the data are correctly formatted based on the input from whence they
came, then there's no reason not to just convert all to dd-mm-yyyy; it's
a "do-nothing" for those that already are, so there's no penalty.

Auric's function will let you do it selectively, but there's probably
not enough of a time penalty that it makes any difference whether use it
or not.

Where you've still got a problem is if the data were imported but not
internally coded to match the external format; then you've still got the
issue that the values of ambiguous dates can't be distinguished solely
by their string value altho as GS points out, inside Excel they will
have been read according to the system default format in which case
switching the formatting changes the appearance in the cell but not the
value which raises the question of whether the dates in the sheet are
actually correct or not depending upon the input source and the system
setting and in what form the values were on input--was it from a date
string or a serial number?

--