Entering minutes and seconds in excel

MKaye02

New Member
Joined
Feb 18, 2016
Messages
2
I am creating a new KPI (Key Performance Indicator) dashboard for my company. One of the stats included is our ASA (Average Speed of Answer) and I'm trying to input the weekly numbers for each department and they have a MTD & YTD portion that will give us the overall average.

The format of this stat is in minutes and seconds or just seconds, for example 7 minutes and 33 seconds is one of them. When attempting to add this into excel it is automatically making it a 7:33pm which is NOT at all what I need.

HELP!!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

Fortunately the math is still the same (same number of seconds in 1 minute, as there are minutes in 1 hour).

You should be able to adjust by just dividing the cell that contains the hours:minutes by 60 to get it into minutes:seconds
 
Last edited:

rajm11

New Member
Joined
Feb 18, 2016
Messages
22
Hi i have recently came across similar problem, what I did is if the time is in seconds then divide that cell by 86400 i.e. cell/86400 and then format it to time excel will give you time in format of hh:mm:ss for the given seconds.
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
The format of this stat is in minutes and seconds or just seconds, for example 7 minutes and 33 seconds is one of them. When attempting to add this into excel it is automatically making it a 7:33pm which is NOT at all what I need.

(7:33 would be interpreted as 7:33 AM, not PM. No matter....)

Enter it as 7:33.0 or 0:7:33 .

Format cells as Custom [m]:ss .

The use of [m] instead of simply m allows for displaying more than 59 minutes.

PS: The Formula Bar displays that time as 12:07:33 AM. Don't worry about it. The FB has its own formatting rules. All that should matter is how it appears in the cell.
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
PS....
I am creating a new KPI (Key Performance Indicator) dashboard for my company. One of the stats included is our ASA (Average Speed of Answer) and I'm trying to input the weekly numbers for each department

In my previous response, I assumed you are manually inputing the data, since your subject line is "Entering minutes and seconds ...".

If, instead, the data is in a text file as 7:33, for example, Excel will always interpret that as 7h 33m. There is nothing you can do to avoid it, short of writing a VBA procedure to read the data yourself.

After the data file is input into A1:A100, for example, do the following to convert it manually.

1. Enter the formula =A1/60 into B1, for example, and copy the formula into B2:B100.
2. Copy B1:B100 and paste-value into A1:A100, formatted as Custom [m]:ss .
3. Delete the formulas in B1:B100.

Caveat: The result of the arithmetic A1/60 might differ from the time constant 7:33 infinitesimally. Note that the formula =MATCH(B1,A1,0) might return a #N/A error. (Actually, 7:33 is okay. But it might not be for other times.) If you might do comparisons with the values in A1:A100, or if you want an "exact" internal representation for other reasons, it would be prudent to enter the following formula into B1 instead: =--TEXT(A1,"\0\:h:m"). The double negate converts text to numeric time.
 
Last edited:

Forum statistics

Threads
1,148,276
Messages
5,745,806
Members
423,976
Latest member
vladm1010

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top