Hello everyone! Did you know…Microsoft Excel can convert text to date.
? interaction ?
Using DATEVALUE Microsoft Excel can convert text to date
As an example, if we downloaded dates from our system as part of a report and a date is 20 September 2015, Microsoft Excel might recognise this as text and not date. To convert text to the proper date time serial number code, use the DATEVALUE function to get 42267 which we can then format as a date to get 20/09/2015.
Using LEFT, MID, RIGHT and DATE Microsoft Excel can convert text to date
Another example might be if we had downloaded for 20 September 2015 the text 20150920. In this instance we use the text functions: LEFT, MID and RIGHT to extract the 2015, 09 and 20 from this text and use these as the arguments in the DATE function, i.e. = DATE(2015,09,20).
Additional Info
So funny!
It’s funny though, if you key directly into a spreadsheet 20 September 2015 then Microsoft Excel will recognise immediately that you are trying to input a date. So when I tried doing this today on a blank spreadsheet I got 20-Sep-15. This says to me that Microsoft Excel usually recognises what looks like a date as a date and this is confirmed by selecting General from the Number group on the Home ribbon tab. You should get a number over 40,000 signifying a date which is after the middle of 2009. In contrast, my video concerns itself really only with what might happen with dates which are downloaded from a system, rather than those directly input.
This is interesting…
This attempt by Microsoft Excel to convert text to date with DATEVALUE is worth examining further. I tried out some more conversions – some worked, others didn’t. With “2015-09-20” no problem, but by switching this around a bit to “2015-20-09” I got an error. The same type of experience with “20/9/2015” which was fine but “9/20/2015” did not work at all. This last one is obviously impacted by my regional date setting. The plot thickens!