Hello Excellers welcome to another #formulafriday blog post where we will look at Excel time. Welcome back if you are a regular and a warm welcome if you are new. I invite you to follow me on Twitter, YouTube, Facebook and sign up to my free Monthly Excel Newsletter where I share three Excel Tips direct to your inbox.

So, back to some more formula fun. Today let’s look at working with time in Excel. In particular how to convert a number into minutes and seconds.

More specifically, converting a number into minutes and seconds.  Let’s say you have a data extract of numbers, a bit like in the screen shot below (column B). These however, are not just numbers, they represent timings in a test script.  They are actually time records in seconds.  What we need to do is to communicate the results in minutes and seconds to the test team.  Seems simple enough right?.  It is once we understand how Excel deal with time.

Regardless of how you format a cell that contains a time value, Excel stores the data as a serial number represented by decimal fractions.  We can run through the basics quickly. In Excel, one day is represented by 1.  So, therefore, think of time as a fractional value of 1. 

Here are some examples which may clarify the way Excel deals with time.
There are 24 hours in a day, 60 minutes in each hour and 60 seconds in each minute.  Therefore, the calculation of the number of seconds in one day is 
=24*60*60 which is 86400 seconds.

Back to our time tests.  We need to convert the times to minutes and seconds to communicate the test results to the team.

We know that Excel stores the number of seconds as a fractional value, so we just need to divide the number of seconds by 86400.  (That is correct we do not just divide the seconds by 60,  the result of that would be minutes and decimal fractions of a minute, not minutes and seconds).

This all looks good for our results to be presented to our Test Team.

I have formatted the test results with a full format of Hours, Minutes and Seconds.  There are however a number of custom format to display time in Excel. 

To explore the options available for your time formatting –

Home Tab | Number Group | Drop Down Box | Time | Custom

That’s it folks, how to convert a number in Excel to minutes and seconds. Have you had experience of converting time values in Excel.

Other Related Blog Posts

Calculate How Long A Task Will Take Using CONVERT Function – Formula FridayU

If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below

How To Excel At Excel – Formula Friday Blog Posts.