#65 Date cells being represented as a number in merged changes sheet

[SimonC]

Hello

Have an issue in my merged differences workbook. My original compared spreadsheets have a column formatted for date. When changes are noted, the merged diff workbook shows the dates as a 5 digit number. I can re-format the affected cells and most of these numbers will revert to dates, but not all. Have attached a merged diff workbook for you.

Cheers, Simon

[SteveT]

I have run a compare with dates. As stated, the merged diffs report shows numbers. This is because the cell is formatted with dates, the cell contents is a datetime value, not text. When both are concatenated in the cell, the result is not valid.

I will look back because I did changed the code to read the underlying value (.value2) instead of the displayed value (.value) because someone came across an issue (can't remember the exact details), so I don't want to implement a 'fix' that undoes another 'fix'!

As a work around, you could try copy and paste values on copies of the worksheets. Excel will then probably concatenate the text instead of the underlying value.

Update: Found the original problem and working on a work around at the moment.

Thanks, Steve

[SimonC]

We're close Steve !

Have attached the latest merged diff sheet for you. Still showing dates as numbers but this time only for the entries appearing in red. Also a new conundrum. The column marked "Last Activity" has a formatting issue (text as number) but also errant entries of 00/01/1900.

Over to you.
SimonC

[SteveT]

The last activity column is formatted for dates which is why n/n/1900 is displayed. This format should have been copied from the 'new' workbook. Is it this way in the original file? Might be worth sending me the originals.

The dates shown as numbers are formatted as General not Dates which is why numbers are shown. Again, this should have been copied from the 'new' file. Can you check this as well (or attach files).

Thanks,

Steve

[SimonC]

Original files attached for you Steve

Cheers, SimonC

[SteveT]

Looking at your files, the Last Activity column is formatted for dates, which is why the report shows it as a date. In your original files, you can check the formatting and then edit the cell (F2), then accept the data (ENTER) and it will change to value to the cell's format.

I found a bug in the latest version that I need to fix. I can see if there is any other issue after fixing that.

[SteveT]

As a side note, you realise that you can do a Database style compare with column 2 as the Key? This may give a better output.

[SimonC]

Steve just d'loaded and installed V1.34.2 and everything seems to be working very well. Do appreciate all your efforts.

Cheers, SimonC