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.

Time dataset with values in 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)
Enter the formula in cell B2
  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.
Copy the formula for all cells
  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).
Click on the dialog box launcher
  1. Within the 'Number' subtab of the 'Format Cells' dialogue box, select the 'Custom' option from the 'Category' drop-down menu.
Click the Custom option
  1. Just put mm:ss in the "Type:" box.
Enter mmss in the field

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

Result shown in minutes and seconds

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.

Time dataset with values in 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.

TEXT formula to convert seconds into minutes

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.

Time dataset with values in 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.

INT MOD formula to convert seconds into minutes

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:

"Transform Your Gaming Experience with the Top 10 Wii to HDMI Adapters of 2023: A Detailed Reviews and Buying Guide"
"Transform Your Gaming Experience with the Top 10 Wii to HDMI Adapters of 2023: A Detailed Reviews and Buying Guide"

Numerous individuals possess a Wii console but are unable to use it due to improper cables or incompatibility with their TV or monitor. However, you can effortlessly connect a Wii console to any TV utilizing an adapter since all modern TVs and monitors support HDMI connections. Thus, we have

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-02 01:43:59
"Upgrade Your Gaming System Now with the Top 5 HDMI Adapters for Wii [2023 Edition]"
"Upgrade Your Gaming System Now with the Top 5 HDMI Adapters for Wii [2023 Edition]"

Do you remember how the Nintendo Wii revolutionized gaming? Its motion controls were a game-changer and the available games, such as Wii Sports, were amazing. But as technology progressed, the Wii became outdated, and playing it nowadays is challenging because of video output compatibility

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-02 00:13:36
A Comprehensive Guide to Using TerraMatch
A Comprehensive Guide to Using TerraMatch

The Convert Time Stamps feature can be employed to transform the format of time stamps. A range of conversions are supported, including GPS seconds-of-week to GPS standard time, GPS time to GPS standard time, GPS seconds-of-day to GPS seconds-of-week, Unix time to GPS standard time, UTC seconds-of-day

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-02 00:12:09
Unlock the Secret to Transferring Gift Cards to PayPal!
Unlock the Secret to Transferring Gift Cards to PayPal!

Unleash the potential of that neglected gift card by transferring its value to your PayPal account! Not only will you bolster your balance, but you’ll open up a world of spending opportunities. But how does one go about making this shift? Let’s dive into the basics so you can make the move with

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-01 00:10:31
Showing page 1 of 10

VyConvert - since 2022
, US

Facebook| | DMCA

Gen in 0.0752 secs