Thread: need help reformatting a date from old dos file
View Single Post
 
Old 01-06-2017, 09:10 AM
xor xor is offline Windows 10 Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
The problem you experienced may be due to the fact that I use Danish settings.

The formula: =--(RIGHT(A1,2)&"-"&MID(A1,3,2)&"-"&LEFT(A1,2)) where A1 = 101202 does the following:

RIGHT(A1,2) takes the two rightmost digits of 101202 that is 02 and concatenates this with a hyphen (which is what I use as separator between day month and year). This is then concatenated with the result of MID(A1,3,2) which takes two digits from 101202 starting at position 3. This again is concatenated with first a hyphen and then with the result of LEFT(A1,2) which takes the two leftmost digits of 101202, that is 10. The result being: 02-12-10 which is a text string. To convert this text string to a date I put double minus immediately after the equal sign. First minus sign convert the text string to a (negative) number and the second minus sign convert the negative number to a positive (date)= 02-12-2010 which on my system means 2nd December 2010.

You can check that conversion by highlighting (in the Formula field) exactly the following part of the formula:

-(RIGHT(A1,2)&"-"&MID(A1,3,2)&"-"&LEFT(A1,2)) that is with only one of the minus signs and press F9. You will see -40514 where 40514 is the serial number for the date 2nd December 2010. If you next try to highlight the whole formula including both minus signs and press F9 you will see the result 40514.
Reply With Quote