SOLVED

'minutes and seconds' data entering in data base

Copper Contributor

Hi, I need to prepare data to compare/analyze time actually spent per transaction with planned time (minutes and seconds), and I faced an issue that it is not convenient to enter time in excel.  

Once cell is formatted to "time" even if I did customized formatting to "m : sec"  it is appearing full date and time like: 18.05.2021 20:02:02. But I need only minutes and second and fast and simple possibility to enter this information say:  2 min and 15 seconds. But often format is changed back to hours : mins : secs, and there are a lot of data entry mistakes which make impossible data analysis. 

 

Please advise some easy and simple data entry option for minutes and seconds

4 Replies

@IB800 

  Date and time functions (reference)

To get detailed information about a function, click its name in the first column.

Note: Version markers indicate the version of Excel a function was introduced. These functions aren't available in earlier versions. For example, a version marker of 2013 indicates that this function is available in Excel 2013 and all later versions.

 

Calculate the difference between two times

Let's say that you want find out how long it takes for an employee to complete an assembly line operation or a fast food order to be processed at peak hours. There are several ways to calculate the difference between two times.

 

 

I would be happy to know if I could help.

 

I wish you continued success with your Computer

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

@IB800 

Entering the time you need to enter hours, like 0:2:15. Alternatively you may enter it as 2:15 which means 2 hrs 15 min, but in calculation divide such values on 60. You may test with ="2:15"/60

 

 

best response confirmed by IB800 (Copper Contributor)
Solution

@IB800  wrote:  ``even if I did customized formatting to "m : sec" it is appearing full date and time like: 18.05.2021 20:02:02``

 

Don't confuse the way that a cell value appears in the Formula Bar with the actual cell value.

 

Enter time in form 2:15.0 (although you might write 2:15,0 if you use comma for the decimal point).  The decimal point tells Excel that the value to the left of the colon is minutes and to the right is seconds.

 

If the time exceeds 60 minutes, you can still enter it in that form; for example, 78:15.0 for 78 min 15 sec.

 

You must also choose the Custom format [m]:ss .  The use of "[m]" instead of "m" displays minutes greater than 59.

 

With 2:15.0 formatted as [m]:ss, the cell displays 2:15, but the Formula Bar displays 12:02:15 AM (in my region; YMMV depending on regional settings).

 

But note:  If we enter only time, the Formula Bar will not show a date.  And even if did, the date would appear to be effectively 0 Jan 1900 in some form, depending on regional settings.

 

-----

 

Possible TMI....

 

OTOH, we can enter date and time in the form 5/18/2021 123:45.67 for 18 May 20021 123 min 45.67 sec with my regional settings.

 

The Formula Bar will display 5/18/2021 2:03:46 AM, again with my regional settings.

 

If we include the date in the cell value, we must use either the Custom format m:ss or h:mm:ss .  The first will show minutes modulo 60.  If we try to use Custom [m]:ss, the number of minutes will be 63841083 (!), including the date serial number times 1440 (i.e. in minutes) !

 

Bottom line:  Do not include date if you want to display time in minutes, especially if the time exceeds 60 minutes.

 

Finally, because the Formula Bar displays time accurate only to the second, we cannot edit the milliseconds of a time value, even if the cell is formatted as [m]:ss.000 .  Instead, we must re-enter the entire time.  It's yet-another limitation of Excel; nothing we can do about it.

 

-----

 

Re: ``But often format is changed back to hours : mins : secs``

 

Regrettably, yes.  When we re-enter some formulas, sometimes Excel automagically chooses a time format, even if we explicitly set the cell to a Custom format.

 

I'm afraid there is no solution for that, other than to beware of it and to reset the cell format after re-entering the formula, if necessary.  Sigh.

 

PS.... Sometimes, there is a work-around that involves disguising the time values involved in the calculation, IIRC.  If you post representative formulas, data and cell formats, we might be able to offer a reasonable work-around.  Then again, maybe not.  Excel can be tenacious (sigh).

Thanx I have idea for work-around, that I will prepare my data input with separate cell for H, separate cell for M and separate for S (like normal numbers) and will use =time(h; m; s) formula to convert those number info in time format to compare with my budgeted time for each transaction.
This will address my major concern, that manual time information input in excel is not convenient and could create a lot of mistakes.
1 best response

Accepted Solutions
best response confirmed by IB800 (Copper Contributor)
Solution

@IB800  wrote:  ``even if I did customized formatting to "m : sec" it is appearing full date and time like: 18.05.2021 20:02:02``

 

Don't confuse the way that a cell value appears in the Formula Bar with the actual cell value.

 

Enter time in form 2:15.0 (although you might write 2:15,0 if you use comma for the decimal point).  The decimal point tells Excel that the value to the left of the colon is minutes and to the right is seconds.

 

If the time exceeds 60 minutes, you can still enter it in that form; for example, 78:15.0 for 78 min 15 sec.

 

You must also choose the Custom format [m]:ss .  The use of "[m]" instead of "m" displays minutes greater than 59.

 

With 2:15.0 formatted as [m]:ss, the cell displays 2:15, but the Formula Bar displays 12:02:15 AM (in my region; YMMV depending on regional settings).

 

But note:  If we enter only time, the Formula Bar will not show a date.  And even if did, the date would appear to be effectively 0 Jan 1900 in some form, depending on regional settings.

 

-----

 

Possible TMI....

 

OTOH, we can enter date and time in the form 5/18/2021 123:45.67 for 18 May 20021 123 min 45.67 sec with my regional settings.

 

The Formula Bar will display 5/18/2021 2:03:46 AM, again with my regional settings.

 

If we include the date in the cell value, we must use either the Custom format m:ss or h:mm:ss .  The first will show minutes modulo 60.  If we try to use Custom [m]:ss, the number of minutes will be 63841083 (!), including the date serial number times 1440 (i.e. in minutes) !

 

Bottom line:  Do not include date if you want to display time in minutes, especially if the time exceeds 60 minutes.

 

Finally, because the Formula Bar displays time accurate only to the second, we cannot edit the milliseconds of a time value, even if the cell is formatted as [m]:ss.000 .  Instead, we must re-enter the entire time.  It's yet-another limitation of Excel; nothing we can do about it.

 

-----

 

Re: ``But often format is changed back to hours : mins : secs``

 

Regrettably, yes.  When we re-enter some formulas, sometimes Excel automagically chooses a time format, even if we explicitly set the cell to a Custom format.

 

I'm afraid there is no solution for that, other than to beware of it and to reset the cell format after re-entering the formula, if necessary.  Sigh.

 

PS.... Sometimes, there is a work-around that involves disguising the time values involved in the calculation, IIRC.  If you post representative formulas, data and cell formats, we might be able to offer a reasonable work-around.  Then again, maybe not.  Excel can be tenacious (sigh).

View solution in original post