Stopwatch-style time format mm.ss

[Sharon1234]

I would like to format my cells so that I can input time between 0 seconds and 1 hour, I would like to format the cell so that it only shows mm.ss

All attempt so far to format the cells has resulted in the typed numbers being changed as the format tries to include hours. Is there a way to make this work?

eg:

4 mins and 30 seconds should be typed and shown as 04.30

56 mins and 1 second should be typed and shown as 56.01

Any suggestions are welcome.

Thank you for your help.

Sharon

 

[Rick Rothstein]

I would like to format my cells so that I can input time between 0 seconds and 1 hour, I would like to format the cell so that it only shows mm.ss

All attempt so far to format the cells has resulted in the typed numbers being changed as the format tries to include hours. Is there a way to make this work?

eg:

4 mins and 30 seconds should be typed and shown as 04.30

56 mins and 1 second should be typed and shown as 56.01

Any suggestions are welcome.

Is what I highlighted in red actually what you are typing into your cells? Please clarify.

 

[Sharon1234]

Hi

Thank you for your interest:

No, I type 4.30 and 56.1 and excel changes it in a very odd way, I can't get this format to stick unless I keep it as a normal number.

Thank you in advance for your help.

Sharon

 

[Rick Rothstein]

Thank you for your interest:

No, I type 4.30 and 56.1 and excel changes it in a very odd way, I can't get this format to stick unless I keep it as a normal number.

Okay, if you want to enter 4.30 for a time of 0:04:30 and have it become that actual time value, then you will need to use VB code because what you are asking for is to have the value you type in converted to a completely different value and Cell Formatting cannot do that (it can only change the way a number displays, it cannot actually change that value to a different value). Is a VB solution (event code to make it execute automatically) acceptable to you? Let us know so that if the answer is yes, we can create the necessary code (and instructions how to install/use it) for you. Oh, and because it is code, we need to know what cells (which columns, rows or individual ranges) you want to have this functionality.

 

[Sharon1234]

Thank you very much for your help, it sounds more complicated than I thought but I would like to try it.

The spread sheet has several columns and rows:

C2 to FY21

The columns are organised as 'start time' 'stop time' then an empty column, this is repeated 60 times, each with 20 rows.

I would like the values to be limited from 00.00 to 60.00

I would also like to format the cell so that the stop time has to be greater than the start time for each pair. Can I still format the cells for this with your solution?

Thank you for your help.

I look forward to trying out your solution.

Sharon

 

[James Snyder]

Some background information; Excel stores dates as a serial number that starts in 1904 and climbs as time passes. It converts this serial number to a format behind the scenes being presented as directed by the formatting. In a VBA solution, you can build that formatting any way you want, but in column formatting, you are limited to the formats Excel has exposed there.

 

[Rick Rothstein]

Thank you very much for your help, it sounds more complicated than I thought but I would like to try it.

The spread sheet has several columns and rows:

C2 to FY21

The columns are organised as 'start time' 'stop time' then an empty column, this is repeated 60 times, each with 20 rows.

I would like the values to be limited from 00.00 to 60.00

I would also like to format the cell so that the stop time has to be greater than the start time for each pair. Can I still format the cells for this with your solution?

See if this event code does what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As Long, txtTime As String
  If Intersect(Target, Columns("C:FY")) Is Nothing Then Exit Sub
  If Target.Column Mod 3 = 2 Then Exit Sub
  If Target.Value < 0 Or Target.Value > 60 Or (Target.Value - Int(Target.Value)) < 6 Then
    Target.Select
    MsgBox "Time value must be in the range 0.#0 to 60.#0" & vbLf & "where the # cannot be a 6, 7, 8, or 9!"
    Application.EnableEvents = False
    Target.Clear
    Application.EnableEvents = True
    Exit Sub
  ElseIf Target.Column Mod 3 = 1 And Target.Value < Target.Offset(, -1).Value Then
    Target.Select
    MsgBox "Stop Time must be later than Start Time!"
    Application.EnableEvents = False
    Target.Clear
    Application.EnableEvents = True
    Exit Sub
  End If
  Application.EnableEvents = False
  If Target.Value = 60 Then
    Target.Value = TimeSerial(1, 0, 0)
    Target.NumberFormat = "[mm]"".""ss"
  Else
    Target.Value = TimeValue("0:" & Replace(Format$(Target.Value, "00.00"), ".", ":"))
    Target.NumberFormat = "mm"".""ss"
  End If
  Application.EnableEvents = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[Sharon1234]

Hi

Thank you for your help, </SPAN>

However this is not working, I get an error message stating that the time value must be in the range 0-60 etc but it is not allowing any number even within this limit. </SPAN>

I would also like to make graphs from the figures so I would ideally like to convert it into an actual time format, is it possible to do this? I would be happy to use an equation to convert them in separate boxes.</SPAN>

Thanks
</SPAN>
Sharon
</SPAN>

 

[Rick Rothstein]

However this is not working, I get an error message stating that the time value must be in the range 0-60 etc but it is not allowing any number even within this limit. </SPAN>

I would also like to make graphs from the figures so I would ideally like to convert it into an actual time format, is it possible to do this?
</SPAN>

Sorry, I must have copied the wrong one of my attempted solutions. I think this one is the one that will work for you. By the way, you would input something like 12.34 and the will convert this value to a real time, but it will still display as 12.34 (select the cell and look at the Formula Bar to see the cell's value is a real time value).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As Long, txtTime As String
  If Intersect(Target, Columns("C:FY")) Is Nothing Then Exit Sub
  If Target.Column Mod 3 = 2 Then Exit Sub
  If Target.Value < 0 Or Target.Value > 60 Or Round(10 * (Target.Value - Int(Target.Value))) > 5 Then
    Target.Select
    MsgBox "Time value must be in the range 0.#0 to 60.#0" & vbLf & "where the # cannot be a 6, 7, 8, or 9!"
    Application.EnableEvents = False
    Target.Clear
    Application.EnableEvents = True
    Exit Sub
  ElseIf Target.Column Mod 3 = 1 And Target.Value < Target.Offset(, -1).Value Then
    Target.Select
    MsgBox "Stop Time must be later than Start Time!"
    Application.EnableEvents = False
    Target.Clear
    Application.EnableEvents = True
    Exit Sub
  End If
  Application.EnableEvents = False
  If Target.Value = 60 Then
    Target.Value = TimeSerial(1, 0, 0)
    Target.NumberFormat = "[mm]"".""ss"
  Else
    Target.Value = TimeValue("0:" & Replace(Format$(Target.Value, "00.00"), ".", ":"))
    Target.NumberFormat = "mm"".""ss"
  End If
  Application.EnableEvents = True
End Sub

 

[Sharon1234]

Thank you for your help, I have been trying to get this code to work, however it has a few bugs: it is not working for all columns, and it also will not keep the format if I change a number (it goes back to the old format of trying to include hours).</SPAN>
I think I would like to try another way.</SPAN>

• I have figured out how to get the numbers to seconds (in additional boxes):</SPAN>
  • =TRUNC(F2)*60 + (F2-TRUNC(F2))*100</SPAN>
• And then to make this into a time (in further additional boxes):</SPAN>
  • =F44/86400 (I also changed the format to show mm.ss)

</SPAN>
This seems to be working well and I can use the converted figures for graphs.</SPAN>

However my other problem is that I want to only allow certain numbers in the table to prevent errors when inputting numbers, I would like the numbers to be limited to:</SPAN>

• 0-60 (this I can do with the pre set options but not in combination with the others)</SPAN>
• 0.#0 where the # cannot be a 6, 7, 8, or 9</SPAN>
• Start time<Stop time

</SPAN>
Is there an equation I can use in the data validation section to allow this? I have tried variations on the following but they are not working:</SPAN>

=IF(OR((AND(C2>=0.00,C2<=.59)),(AND(C2>=1.00,C2<=.1.59)),C2,””)</SPAN>
=IF((AND(C2>C3))(OR((AND(C3>=0.00,C2<=.59)),(AND(C3>=1.00,C3<=.1.59))),C3,””)
</SPAN>
I’m not sure if I have the equation wrong somewhere or if I am asking too much of this function. There may be a simpler way of formatting this but I am not familiar with all the symbols and equation options yet.
</SPAN>
Can you help me with this or will I have to use a code to get this to work?

Thank you for all your help.

Sharon</SPAN>