Home » Forums » Open Source Learning Center » Question and Answer

Date beyond 31st Dec 9999

Submitted by dineshexcel on 13 June, 2012 - 13:30

Hi,

I have a huge data set (ranging from A1:CH6000)with few date columns. I have some date values which are beyond 31st Dec 9999, so excel is displaying it as "########". My objective is to convert these dates into 31st Dec 99 (12/31/99). I need a macro code for this requirement,my date columns are "J" and "K". Can you please help in this regard?

‹ If in cell is a value, copy rows to sheet2 populate new "log" worksheet from identical invoice templates in three other sheets ›
»
Nick's picture

where do you get a date of

Submitted by Nick on 14 June, 2012 - 12:36.

where do you get a date of 31st Dec 9999 from ?
... easy to do.. just subtract 2885415 (the difference in days between 31/12/9999 and 31/12/2099

»