Prevent cell comment showing Time as Decimal i.e. 12:00 as 0.5

Hello,
I am using the following code that keeps a recored of all cell entries required for my workbook. A cell audit trail if you like.
But if the cell value is a time e.g. 12:00, when its put into the comment it shows as a decimal e.g. 0.5.
Can anyone see what I can change to stop this.

Please Login or Register  to view this content.

Your problem seems to be the way Excel treats time. Excel 'Date' is a separate 'type'. Excel 'dates and times' are also type 'Date'. With 'time only' even though in Excel you can declare a type 'Time', it is really internally stored as type 'Double' (fractional value between 0 and 1). That's why 12:00 is displayed as 0.5.

A side effect to the following is that 'actual types of double' that are between the values of 0 and 1 may lose precision in your comment and will be displayed with the number of decimals you see when looking at the cell. For example the number 0.12345678 with number format showing two decimals, will appear in the cell as '0.12' and '0.12' will appear in your comment.

In your Macro try the following (tested in Excel 2003):

Please Login or Register  to view this content.

Hey LJMetzger, Thanks for the reply, only just got round to checking for responses.
Your idea works perfectly, which made me think, why dont I just change the following line:

Please Login or Register  to view this content.

Has the same effect! Cant believe I couldnt see it? This way, it shouldnt matter what is entered into the cell, cos it will just copy it exactly.

Thanks, wouldnt of seen it without your input.