Extract first 2 numbers from MM/DD/YY data (Excel 2000)

Topic

New Reply

Hi, I have a column of data in the following format: MM/DD/YY. For example, 10/15/04. In the adjacent column, I’d like to display the 10 only. I have tried = left(x2, 2). Data is in column X… It returns 38275.6137934028… Any ideas on how to extract only a 10 here???

All help appreciated.

Thanks.

Reply | Quote

Replies

Use =MONTH(X2)
Excel stores dates as numbers internally – the number of days since December 31, 1899. So the LEFT function is not useful to extract a portion of a date, but Excel has DAY, MONTH and YEAR functions.

Reply | Quote

Hans, thanks for your reply… I tried the =month(x2) formula and it returns the following: 1/10/00

Any other ideas?

Reply | Quote

Apparently, X2 is now formatted as a date. Select X2, then select Edit | Clear | Formats…

Reply | Quote

Hans, same result… See attached. By the way, thanks!

Reply | Quote

Sorry, I should have written: select the cell that contains the formula (instead of X2), and select Edit | Clear | Formats…

Reply | Quote

Sorry, I should have written: select the cell that contains the formula (instead of X2), and select Edit | Clear | Formats…

Reply | Quote

If I select cell D2 in your worksheet, then select Clear from the Edit menu and then select Formats from the fly out menu, the cell displays 10.

Reply | Quote

If I select cell D2 in your worksheet, then select Clear from the Edit menu and then select Formats from the fly out menu, the cell displays 10.

Reply | Quote

Hans, I went into the other cell where I had entered the (cell z2 had the formula) formula = month(x2) and followed your instructions, edit, clear, formats and that is a fix.

As always, THANKS!

Reply | Quote

Hans, I went into the other cell where I had entered the (cell z2 had the formula) formula = month(x2) and followed your instructions, edit, clear, formats and that is a fix.

As always, THANKS!

Reply | Quote

Hans, same result… See attached. By the way, thanks!

Reply | Quote

Apparently, X2 is now formatted as a date. Select X2, then select Edit | Clear | Formats…

Reply | Quote

Hans, thanks for your reply… I tried the =month(x2) formula and it returns the following: 1/10/00

Any other ideas?

Reply | Quote

Use =MONTH(X2)
Excel stores dates as numbers internally – the number of days since December 31, 1899. So the LEFT function is not useful to extract a portion of a date, but Excel has DAY, MONTH and YEAR functions.

Reply | Quote