Dates behaving erratically in Excel

[cdfjdk]

Hi,

I have used Text to Columns to convert the column to DMY and have a custom cell format

[$-809]dd mmm yyyy

so, for example, the pre-entered value 01/03/2018 displays correctly as 01 Mar 2018.

However, if I try to change the date, e.g. change 01/03/2018 to 03/03/2018, it inserts the change as a second value, e.g. the cell now contains "01/03/2018, 03/03/2018" (i.e. it becomes a text string, not a date). The second value cannot be deleted - the entire field must be deleted.

Also, even though [$-809] specifies UK date format and the PC's regional date setting is dd/MM/yyyy, if I re-enter 01/03/2018, which should be 1 Mar, the date is converted to US date format 03/01/2018, which is 3 Jan.

I'm really confused - any suggestions welcome!

Excel 2010 by the way.

 

[RoryA]

Sounds like you have some code in the worksheet's change event (or the workbook_sheetchange event).

 

[cdfjdk]

Sounds like you have some code in the worksheet's change event (or the workbook_sheetchange event).

Thanks, Rory - I do have a workbook_sheetchange event, but it is column-specific and doesn't apply to the date columns.

 

[RoryA]

I can't think of anything else that would cause what you describe. If you disable macros, does the same thing happen?

 

[cdfjdk]

Thanks, Rory, they're really useful tips - yeah, it's somewhere in the macros or the workbook_sheetchange, all of which have been working perfectly up until the other day. Sigh! I have some housekeeping to do!