I need help to convert a birthday in one format to another w/o using the cell formating by using EXTEXT and DATEDIF.
example: 901101 -> 1990-11-01
I used to know how to do this in older versions of Excel, but can't figure it out in the newer version I have now. Sorry, but maybe somebody else does.
That is ok. I will figure it out. I appreciate that you are honest with me and that you took time to answer.
I agree with Reed.
The unfortunate thing is that, as Microsoft and other companies think they are improving things with each new version, they are really making everything more complex and difficult to use. The worst thing I've seen lately is "the ribbon" that MS has put on different Office programs. =(
To convert a birthday from one format to another using EXTEXT and DATEDIF functions in Excel, you can follow these steps:
Step 1: Enter the birthday in the original format (e.g., 901101) in cell A1.
Step 2: In cell B1, use the LEFT function to extract the year part from the original date. In this case, you can use the formula:
`=LEFT(A1, 2)`
Step 3: In cell C1, use the MID function to extract the month part from the original date. In this case, you can use the formula:
`=MID(A1, 3, 2)`
Step 4: In cell D1, use the RIGHT function to extract the day part from the original date. In this case, you can use the formula:
`=RIGHT(A1, 2)`
Step 5: In cell E1, use the CONCATENATE function to combine the extracted parts and create the new formatted date. In this case, you can use the formula:
`=CONCATENATE("19", B1, "-", C1, "-", D1)`
Step 6: Finally, you can see the converted birthday in the new format (e.g., 1990-11-01) in cell E1.
Note: If the original year is beyond the 20th century, adjust the CONCATENATE formula accordingly.
This method allows you to convert the birthday from one format to another without relying on cell formatting functions like EXTEXT and DATEDIF. Instead, it extracts and rearranges the parts of the original date using Excel's string manipulation functions.