Hello friends,
I want to convert 3-digit and 4-digit numbers as proper valid dates in the following format
3 digits
d-mmm-yyyy
d-mmm
dd-mmm
4 digits
d-mmm-yyyy
dd-mmm-yyyy
dd-mmm
For example:
* If Cell A1 has 111 -> corresponding cells should have 1-jan-1991, 1-jan-2001, 11-Jan, 1-Nov (that is d-mmm-yyyy, d-mmm-yyyy, d-mmm, dd-mmm)
* If Cell A1 has 1111 -> corresponding cells should have 1-Nov-2001, 1-Nov-1901, 11-Jan-2001, 11-Jan-1901, 1-Jan-2011, 1-Jan-1911, 11-Nov (that is d-mmm-yyyy, d-mmm-yyyy, dd-mmm-yyyy, dd-mmm-yyyy, d-mmm-yyyy, d-mmm-yyyy, dd-mmm)
* If cell A1 has 456 -> corresponding cells should have 4-May-1906, 4-May-2006 (that is d-mmm-yyyy, d-mmm-yyyy)
* If Cell A1 has 3239 -> corresponding cells should have 3-Feb-1939, 3-Feb-2039 (that is d-mmm-yyyy, d-mmm-yyyy)
If a particular 3-digit and 4-digit number does not convert into a proper valid date, the corresponding cell should remain blank. Year should be 20th and 21st centure (1900-1999 or 2000-2099)
I am struggling since past couple of days and I tried splitting numbers, putting filters, concatenate formulas etc but I am not able to set all conditions. I thought macro based solution or a user defined function would be better but I am unable to write it and need help. Appreciate if I can get some start here...
Thank you.
Bookmarks