Entering minutes and seconds in excel

[MKaye02]

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

 

[Jonmo1]

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

 

[rajm11]

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]

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]

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.