Viewers and clients frequently ask me how to convert times displayed as military time (1700 hours) to standard time (5:00 PM). Many conpanies capture time using the 24 hour clock (military time) however, many end-users, particularly in the United States want to see time expressed as standard time with AM and PM designations.
Digital Time
Time Stored as Text Value or as a Number?
The first step in the conversion is to determine how the time value is “stored” in Excel. Is it stored as a “text value” or is it stored as a numeric value? This will determine how we convert military time to standard time. In this lesson, I demonstrate how to extract text characters, join text strings and then use the TIMEVALUE() Function in Excel to make this conversion.
Use Text Functions and the “&” (ampersand)
Next, I use the LEFT() function to extract the two left-most characters from the text string (0100) and the RIGHT() function to extract the two right-most characters. In Excel, you use the “&” (ampersand) to join text strings. When you insert the “:” (colon) between the hours and the minutes, be sure to enclose it inside ” “(double quote marks).
The TIMEVALUE() Function
In order to perform time elapsed calculations in Excel, you need to have time values “stored” as numeric values. You can use the “visual clue” – to which side of the cell is the time value aligned? Text values align to the left side of the cell; numeric values align to the right side of the cell. After you use the TIMEVALUE() function, be sure to format the numeric time values to meet your needs.
If you import time values that are “stored” as numbers – they align to the right side of the cell – but are formatted as military time (13:30) all you have to do is change the fomratting to standard time (1:30 PM).
Follow this link to watch this video lesson in High Definition, Full Screen mode on YouTube. My channel is DannyRocksExcels.
I invite you to subscribe to my video podcast, “Danny Rocks Tips and Timesavers,” available for free on iTunes. Click here to view my videos at the iTunes store.
Visit My New Online Shopping Website
I invite you to visit my new, secure shopping website – http://shop.thecompanyrocks.com – where you can view all of my products and “Learn how to “Master Excel in Minutes – Not Months!”
