Microsoft Excel – changing time from 1400 to 14:00

Sometimes we are given a column of times in a worksheet but they don’t look formatted as we went them to be, e.g. 1400 instead of 14:00.

Excel time shots

If the times are as shown in column A above, we need to find the length of each “time”to be able to put the colon in the right place. If the length is 4, we need to put a colon after the first two numbers, if the length is 3, we need to put the colon after the first number, i.e. we need to subtract two from the length of the “time”.

The LEFT function takes the specified number of characters from the left of a string, in this case the length of the string -2. The LEN function allows us to find the length of the string. The & is our joining operator and the colon has to be enclosed in double quotes as we are creating a text string. Finally we need the RIGHT function to give us the last two characters from the string.

Find out more about our Microsoft Excel training Sydney, Australia and our Microsoft Excel training London,  Middlesex, Surrey or our Microsoft Excel training Dorset, Hampshire, Wiltshire UK.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s