none
Entering Time data in excel in mm:ss format (without the leading 0: for the hours)

    Question

  • Hi, I want to enter time data into excel sheet in mm:ss format. The problem is - that although the cell format is mm:ss, if I enter the data like this:

    12:33 the actual value stored is 12 hours and 33 min. if I want 12 min and 33 sec, I need to enter 0:12:33

    Is there a way to make the cell accept 12:33 as mm:ss ?

    Thanks, Dani


    Dani Kaplan
    Thursday, January 20, 2011 9:04 AM

Answers

  • You could have a Worksheet_Change event procedure in the module belonging to the worksheet that divides the value entered by 60

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      Dim R As Range
      If Intersect(Target, Me.Range("TimeCells")) Is Nothing Then Exit Sub   ' assumes range name for the cells for time input is TimeCells
      For Each R In Intersect(Target, Me.Range("TimeCells")).Cells
        Application.EnableEvents = False  ' stop it from happening again!
        R.Value = R.Value / 60
        Application.EnableEvents = True
      Next
    End Sub


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    • Marked as answer by kdani Thursday, January 20, 2011 12:48 PM
    Thursday, January 20, 2011 9:59 AM

All replies

  • You could have a Worksheet_Change event procedure in the module belonging to the worksheet that divides the value entered by 60

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      Dim R As Range
      If Intersect(Target, Me.Range("TimeCells")) Is Nothing Then Exit Sub   ' assumes range name for the cells for time input is TimeCells
      For Each R In Intersect(Target, Me.Range("TimeCells")).Cells
        Application.EnableEvents = False  ' stop it from happening again!
        R.Value = R.Value / 60
        Application.EnableEvents = True
      Next
    End Sub


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    • Marked as answer by kdani Thursday, January 20, 2011 12:48 PM
    Thursday, January 20, 2011 9:59 AM
  • Thnanks Bill, it helps and work as expected. I just need a little help in adding some sort of a check -

    if the user does use the 0:12:23 format, it shouldn't devived by 60 b/c this time, the time is in the correct format.

    maybe if(R.Value > ???) ?

    Thanks,

    Dani

     

     


    Dani Kaplan
    Thursday, January 20, 2011 10:44 AM
  • I initially wrote
      If R.Value >= 1/24 Then...
    but that means that if the user enters 0:15 it will be treated as 15 minutes rather than 15 seconds.

    If you never enter times shorter than a minute then that test will work.

    Another option would be to test for seconds being zero.
      If R.Value >=1/24 Or Right(R.Text,3)=":00" Then
        ...
      End It

    That would only go wrong if the user entered e.g. 0:15:00 meaning 15 minutes. 0:15:01 would be treated OK.

    Unfortunately there is no way to know what the user actually typed unless I guess if you pre-format the cells as text.
    You could then analyse the text entered, then change the cell's format to "mm:ss", formulate the correct time value from the text and set the cell to that value.  You'd still need to allow for the user going back and changing a cell which was already formatted as mm:ss.
    Is it worth doing that? 


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Thursday, January 20, 2011 11:17 AM
  • Hi, thanks again.

    I've used this condition:

    If R.Value >= 1 / 24 And R.Value <= 2.49930555555556 

    and it works good from 1:00 to 59:59

    I think the only thing that left for me is to count the number of : in the field to determing if the user used 0:12:00 or 1:12:21 to be ignored by the function.

    if it's 2 ( 0:12:12 or 1:01:02) - than do nothing, if It's 1 - divide....

    My Problem now is that R.Text for some reason contains only the mm:ss (b/c of format ?) and the charcount function gets only the mm:ss portion of it.

    is there a way to get what the user typed in the field before formatting ?

    (now if I enter 1:23:23 R.text is 23:23)

    Dani

     

     


    Dani Kaplan
    Thursday, January 20, 2011 11:57 AM
  • As I said in my last response:

    <<
    Unfortunately there is no way to know what the user actually typed unless I guess if you pre-format the cells as text.
    You could then analyse the text entered, then change the cell's format to "mm:ss", formulate the correct time value from the text and set the cell to that value.  You'd still need to allow for the user going back and changing a cell which was already formatted as mm:ss.
    Is it worth doing that? 
    >>


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Thursday, January 20, 2011 12:11 PM
  • Thanks, I've missed the pre-format cell as text.

    I think I'll preformat it as hh:mm:ss it will be as close as possible to give me the solution.

    Thanks for all the help.

    Dani

     


    Dani Kaplan
    Thursday, January 20, 2011 12:20 PM
  • Good idea!
    You can then test for
       Left(R.Text,3)<>"00:" And Len(R.Text)=8 And IsNumeric(R.Value)
    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Thursday, January 20, 2011 1:17 PM
  • Hello,

    I am having the same issue as the original post but I don't understand the solution.  I'm a simple Excel user and don't know what Worsheet_Change events are.  I looked up some stuff about the subject, and attempted to create a macro but I'm getting an error "Run-time error 1004".  Is there any other way to solve this or is this the only way to create mm:ss cells?  Seems like something Excel should fix.


    • Edited by marksskim Sunday, April 3, 2016 1:18 PM
    Sunday, April 3, 2016 1:17 PM