Converting Seconds to Minutes in Excel (Simple Formula)

In Excel, converting a value in seconds to a minute requires more work than simply dividing by 60.

Because Excel stores date and time values in a particular format, this is the case. Time values are represented as fractions or decimals in Excel, while a day is represented as a whole number.

The numbers 44927 and 44927, for instance, both denote the first day of the year 2023. The numeral 25 represents 6:00 AM on January 1, 2023.

Here, 0 The time is represented by the fraction 25, which is a Considering that there are 24 hours in a day, 0 During the 25th hour, which is equivalent to six hours (24*0), the sun would be at its lowest point. 25)

This is why dividing a value of 150 seconds in a cell by 60 results in a value of 2. 5 Since 150 seconds is equivalent to 2 2 and a half days in Excel time is equivalent to 5 minutes

You'll need to adjust your method a bit if you want to go from seconds to minutes and seconds.

Here, I will teach you how to convert seconds into minutes and seconds using a straightforward formula and some clever formatting of numbers.

Custom Formatting of Seconds to Minutes

The data set below shows the finishing times and the number of participants for a few different races that students took part in.

I have the students' names in column A and their completion times in seconds in column B; I need to convert the seconds to minutes and seconds.

Right now, we only have access to whole numbers representing the time it took to finish the race. To illustrate, the value of 103 in B2 indicates that the race was run in 103 seconds.

We need to convert these values in seconds into the fraction of the day so that we can display them in any time value format we want, but as I mentioned earlier, Excel stores days as whole numbers and time values as fractions of that day.

How to express a number of seconds as a percentage of a day is detailed below.

1. Type the following formula into cell C2:

=B2/(24*60*60)

1. Do the same thing for the rest of the column's cells. Filling the remaining cells with the same formula is as easy as doing a copy-and-paste or double-clicking the Fill handle.

1. To do this, select all the values in column C, go to the "Home" tab, and then in the "Number" group, click on the dialog box launcher icon (or use the keyboard shortcut Control 1).

1. Within the 'Number' subtab of the 'Format Cells' dialogue box, select the 'Custom' option from the 'Category' drop-down menu.

1. Just put mm:ss in the "Type:" box.

Follow the steps above to get the minutes and seconds equivalents of the seconds values in column B (illustrated below).

Please allow me to briefly describe how this works.

In the preceding procedures, we accomplished two goals:

• To express it as a percentage of a day, I used the formula to convert the seconds' values. To do this, we converted the seconds values to minutes by multiplying by 86,400 (the number of minutes in a day).
• Custom number formatting was used to display the obtained decimal value as minutes and seconds.

You may be wondering why we didn't just divide the seconds' values by 60, but that's because we've now converted the seconds' values into a number that Excel recognizes as a time value. The values here can be used in calculations and displayed in a variety of formats (like hh:mm:ss).

Use This Formula to Convert Seconds into Minutes

This procedure involves converting seconds to a fraction of a day and then using custom number formatting to display the result in the mm:ss format.

Let me now demonstrate some formula-based strategies for accomplishing this.

The TEXT Methodology

Excel's TEXT function lets you compute a number and then, without leaving the formula, tell Excel how you want that number displayed.

In the table below, column B contains timestamps in seconds, but I'd prefer to display the information in minutes and seconds.

For that, use the following formula:

=TEXT(B2/(24*60*60),"mm:ss")

Fill out the formula in C2, and then replicate it for the rest of the column.

In order to display the time in minutes and seconds, the preceding value first converts the seconds' values to the fraction of a day.

It's important to keep in mind that the text format of the result means these numbers can't be used in any further calculations after they've been obtained using this method.

Applying the Formulas for INT and MOD

In the procedures discussed thus far, the values of seconds have been converted by first dividing them by 24*60*60, so that they now represent a percentage of a full day.

This was done so that the value could be displayed using Excel's built-in, user-defined custom formats.

A slightly different method can be used to simply convert the seconds' values to minutes and display them in a cell.

Here's some data: in column B, I have the elapsed time in seconds; in column C, I'd like to display this information in minutes and seconds.

Here's the formula you can use:

Time in minutes: =INT(B2/60)&" &MOD(B2,60)&"

The formula should be entered in cell C2, and then copied down the column.

Formula-wise, we first converted the seconds' value to minutes by dividing it by 60, and then we used the INT function to extract just the integers from the resulting value.

The value of the seconds tells us how many minutes have passed.

After subtracting 60 minutes, we joined it with the output of the MOD function, which provides the number of seconds that were left over.

I've included "Minutes" and "Seconds" in the aforementioned formula. Formula can be used as-is or adjusted to display data in a variety of formats.

For instance, the following equation can be used in place of the words "minutes" and "seconds"

=INT(B2/60)&":"&MOD(B2,60)

You can use these methods to convert seconds to minutes in Excel.

The correct method involves converting the seconds to a value that represents the fraction of the day, and then formatting the value using Custom Number Formatting.

You can also get a text value (which can't be used in any further calculations) by using the TEXT formula or the INT/MOD formula.

I'm hoping this Excel guide was helpful.

A few more Excel guides to check out: