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
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
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