I have two columns, in the first is the month (i.e. August), in the second is the year (i.e. 2011). However, i downloaded them from a database which formatted them as text and I need to use them in a formula in which it does not recognize these. How can i convert the month to the number of the month and convert the year to a yyyy date as it currently looks?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Converting Text to Date
- Thread starter Mudbutt
- Start date
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,525
- Office Version
- 365
- Platform
- Windows
This formula should work for you....
=--(A2&B2)
You will have format Column C with a Date cell format of your choice.
=--(A2&B2)
You will have format Column C with a Date cell format of your choice.
Upvote
0
Wow! Much simpler than my method!
Upvote
0
Now dats elegant.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,525
- Office Version
- 365
- Platform
- Windows
The parentheses are important too. Also, 1* or 0- could have been used in place of the -- symbols. So, these formulas would have worked as well...
=1*(A2&B2)
=0+(A2&B2)
Excel tries to help its users as much as possible (sometimes it helps too much, but not in this case
). Excel will take text concatenations that create numeric looking values and, when grouped in parentheses, convert them to real numbers if used in a mathematical operation. So, this would produce the real excel number 23...=--("1"&"8")
The -- is a short cut for multiplying by minus one, twice. Since minus one times minus one is a plus 1, the mathematical operatiion converts the date to a number and multiplies it by plus one (as does the first alternative or adds 0 to it as does the second alternative). Now I know what you are saying to yourself... "Wait a minute, I have a date concatenated together, not a number!". Well, yes, that is technically true; however, dates to Excel are just floating point numbers... the integer portion is the number of days offset from an imaginary "date zero" (December 31, 1899 to Excel) and the decimal portion, if any, is the fraction of a 24-hour day represented by the decimal hours since midnight represented by the time part. Here is where Excel goes out of its way to help the user... it sees the text concatentation as being the shape of a date (I think this is where the parentheses comes into play) and ends up converting that text representation of the date into its underlying numeric value in response to the mathematical operation that the -- symbols impose on it. At least that is my interpretation of what is going on.
Upvote
0
