# Entering minutes and seconds in excel

#### MKaye02

##### New Member
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
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
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
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
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:

Replies
6
Views
231
Replies
1
Views
985
Replies
3
Views
381
Replies
3
Views
488
Replies
0
Views
149

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

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

### Which adblocker are you using?

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

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