Here’s how to convert the Last Trade time in Excel’s Stock data type into an Excel date/time that’s usable in other parts of Excel.
UPDATE: near the end of September 2018, the Last Trade Time date format changed to an Excel serial date. No conversion or text parsing necessary. So this tip isn’t necessary any more. We’ll leave it up in case it helps others with date conversion problems.
Stock data type is a new live or linked data type in Excel. It’s currently available to Insiders of Office 2016 for Windows. Stock and index information from across the globe comes directly into a worksheet. Our book Real Time Excel looks at many ways to get the latest financial info into worksheets, including the Stock data type.
A vital incoming field is ‘Last Trade time’.
Last Trade time arrives in Excel as a text string like 6/18/2018 17:43:01 in other words m/dd/yyyy hh:mm:ss . The format is NOT affected by the Windows regional setting.
Converting that into an Excel date and time isn’t easy but it’s necessary for any date/time calculations like this column which shows the time since the last price was received.
=DATEVALUE([@[Last trade time]]) might seem ideal but it only works for the date, not the time component. It also has the Day/Month vs Month/Day problem. You can’t rely on DateValue() to always correctly interpret dates when the string uses digits for the month component.
Instead, it’s best to manually parse the date to ensure that the month is correctly identified.
Here’s a real DATE() function at work to convert a text date and time into one that Excel software can understand.
To convert, a <date text string> like:
6/10/2017 22:33:49
needs something like this:
=DATE(
RIGHT(TRIM(LEFT(<date text string>,FIND(" ",<date text string>))),4),
LEFT(TRIM(LEFT(<date text string>,FIND(" ",<date text string>))),FIND("/",TRIM(LEFT(<date text string>,FIND(" ",<date text string>))))-1),
MID(TRIM(LEFT(<date text string>,FIND(" ",<date text string>))),FIND("/",TRIM(LEFT(<date text string>,FIND(" ",<date text string>))))+1,2))+
TIMEVALUE(TRIM(RIGHT(<date text string>,FIND(" ",<date text string>)-1)))
There are plenty of anomalies and strangeness in the Excel Stock data type but the entire feature is still being tested by ‘Insiders’ in some Office preview versions. Let’s hope Microsoft listens and acts on feedback before the Stock and Geo linked data types are released to the public.
