Keying in time without using colon between hours and minutes

Topic

New Reply

is there a way to key in hours and minutes as regular number, ie; 1345 autoformated as 13:45 or 800 as 08:00? or will it be mandatory to key in the number as 13:45 or 08:00.

I would like this done if possible without a macro.

Thank you.

MNN

Reply | Quote

Replies

is there a way to key in hours and minutes as regular number, ie; 1345 autoformated as 13:45 or 800 as 08:00? or will it be mandatory to key in the number as 13:45 or 08:00.

I would like this done if possible without a macro.

Thank you.

MNN

Is that a choice in Format Cells like the monetary setting? If not you probably do need something else. The only instances where I didn’t want to use the colon was with GMT/UTC/Zulu time with the 24-hour format such as 1458Z, could also be used with 24-hour format and not considering a time zone, e.g. 1845.

I worked in aviation for over 35 years and a 4-digit time was universally understood as 24-hour based upon GMT/UTC/Zulu, it was the same with maritime, both solved the consideration of time zone and was much more accurate. Both activities would cross more times zones in the course of business and hence the need to eliminate confusion.

Before you wonder "Am I doing things right," ask "Am I doing the right things?"

Reply | Quote

MNN,

Place the following code in a worksheet module. Enter your values in column A and they will be converted to time. If I had more time, I would add error checking to make sure it is a valid time (ex 2530)

HTH,
Maud

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not WorksheetFunction.IsNumber(Target) Then Exit Sub
If Not Intersect(Target, Columns(“A:A”)) Is Nothing Then
    X = Target.Value
    Y = Right(X, 2)
    If Len(X) = 2 Then
        Z = “00:”
    ElseIf Len(X) = 3 Then
        Z = “0” & Left(X, 1) & “:”
    ElseIf Len(X) = 4 Then
        Z = Left(X, 2) & “:”
    Else: Exit Sub
    End If
    Application.EnableEvents = False
        Target = Z & Y
        Target = Format(Target, “hh:mm”)
    Application.EnableEvents = True
End If
End Sub

Reply | Quote

Hi MNN

If you really want to do this without a macro, then try this:
Let’s assume your Time entry is in column [A]
Then, click the column [A] heading to select the entire column, and apply this custom format:
00″:”00
46268-rz-format-hhmm
Now, with the same column selected, use Data>Validation to restrict entries to numeric whole numbers between 1 and 2400
46269-rz-data-validation1
Now, if you want to use these time entries in any calculations, you could use a ‘hidden’ column with this formula in cell [B2]:
=TIME(TRUNC(A2/100),MOD(A2,100),0)
..and copied down as required

..see attached example file
zeddy

Reply | Quote

Hi MNN,

Check-out this url on Chip Pearson’s great site:

http://www.cpearson.com/Excel/DateTimeEntry.htm

I can recommend it.

Good luck:

Peter

Reply | Quote

Hi Peter

I would like this done if possible without a macro.

Chip Pearson is always a great source of info.
But, other than post#4, I haven’t seen another way of doing what MNN asks for without a macro yet.

zeddy

Reply | Quote

Hi zeddy,

Whoops! I missed the no macro bit!

Chip’s solution does it in the same cell, which I needed when I used his solution.

Congrats on your solution – great!

Peter

Reply | Quote

Very nice number to time converter zeddy.

cheers, Paul

Reply | Quote

Hi Paul

Thank you for that.
..Excel’s ROUNDDOWN function and TRUNC function are both the same if you want to keep 1 or more decimal places, but, if you want zero decimals, you can just use TRUNC(xxx) rather than ROUNDDOWN(xxx,0)
..and TRUNC is shorter to type than ROUNDDOWN

zeddy
lazy-fingers

Reply | Quote

Hey Y’all,

I’m a little late to this party but since I worked it out slightly differently I thought I’d post just to show a slightly different way of doing the VBA. Yeah I know the OP doesn’t want a macro but it could be useful to someone else.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim X As Variant
  Dim Y As Integer
  Dim Digits As Integer
  
  If Target.Count > 1 Then Exit Sub
  If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
 
  If WorksheetFunction.IsNumber(Target) Then

    Application.EnableEvents = False
    X = Target.Value
    Digits = Len(X)
    
    Select Case Digits
          Case 1
                 Target = "00:0" & X
          Case 2
                 Target = "00:" & X
          Case 3
                 Target = "0" & Left(X, 1) & ":" & Right(X, 2)
          Case 4
                 If Int(Left(X, 2)) > 24 Then
                   MsgBox "Invalid Time Value..." & vbCrLf & _
                          "value must be less than 2400!" & vbCrLf & vbCrLf & _
                          "Please correct!", vbCritical + vbOKOnly, _
                          "Time value out of range!"
                 Else
                   Target = Left(X, 2) & ":" & Right(X, 2)
                 End If
          Case Else
                   Target = 0
                   MsgBox "Invalid Time Value..." & vbCrLf & _
                          "value must be less than 2400!" & vbCrLf & vbCrLf & _
                          "Please correct!", vbCritical + vbOKOnly, _
                          "Time value out of range!"
          
    End Select
    
    Target = Format(Target, "hh:mm")
    Application.EnableEvents = True
    
  End If
  
End Sub

Results:
46272-FormatTimes

Test File: 46273-Excel-VBA-Self-Formatting-Time-Values

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..so, if the User types in 1277 it corrects that to 13:17 rather than rejects it.
Well that’s OK maybe.
Or maybe not.
My posted .xlsx file will display 12:77, but the timevalue is 13:17 too!

I’ll post my vba version tomorrow!

zeddy

Reply | Quote

Zeddy,

You’re a hard task master!

I think this has the bases covered.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim X            As Variant
  Dim Y            As Integer
  Dim Digits       As Integer
  
  If Target.Count > 1 Then Exit Sub
  If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
 
  If WorksheetFunction.IsNumber(Target) Then

    Application.EnableEvents = False
    X = Target.Value
    Digits = Len(X)
    
    Select Case Digits
          Case 1
                 Target = "00:0" & X
          Case 2
                 If Val(X) > 59 Then
                   DisplayErrorMsg 1
                 Else
                   Target = "00:" & X
                 End If
          Case 3
                 If Val(Right(X, 2)) > 59 Then
                   DisplayErrorMsg 1
                 Else
                   Target = "0" & Left(X, 1) & ":" & Right(X, 2)
                 End If
          Case 4
                 If (Int(Left(X, 2)) < 25 And Int(Right(X, 2))  24 Then DisplayErrorMsg 2
                   If Int(Right(X, 2)) > 59 Then DisplayErrorMsg 1
                 End If
          Case Else
                   Target = 0
                   DisplayErrorMsg 2
          
    End Select
    
    Target = Format(Target, "hh:mm")
    Application.EnableEvents = True
    
  End If
  
End Sub

Sub DisplayErrorMsg(MsgNo As Integer)

   Dim Msgs(1 To 2) As String
   
   Msgs(1) = "Invalid Time Value..." & vbCrLf & "seconds (2 right most digits) must be less then 60!"
   Msgs(2) = "Invalid Time Value..." & vbCrLf & "value must be less than 2400!"
   
   MsgBox Msgs(MsgNo) & vbCrLf & vbCrLf & _
          "Please correct!", vbCritical + vbOKOnly, _
          "Time value out of range!"

End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Morning RG

I’ve amended my non-macro version to deal with the 1277 issue in post#10

Now, am I a hard task master??
I’m brutal!!!
You have covered three bases.
To cover ALL the bases, you need to allow the User to edit an existing entry, to say, change the minutes from 10 to 15.
Nearly there.
..of course, we could just tell the User they have to re-enter the new ‘adjusted’ value, and not allow them to edit any existing cells.

zeddy

Reply | Quote

Hi RG

My apologies. It’s Christmas. No time for being brutal. Thank you Paul T for reminding me.
So, RG, you did cover ALL the bases.
..but just one extra line in your code would then allow for edits to the time-cells, and thus knock it out of the park.
(or, as a cricket-person would say, ‘knock-it-for-six’)

Here’s the extra line I put in..
If Target.Value = CDec(TimeValue(Target.Text)) Then Exit Sub ‘<< allows edit to cell

See attached file with your code and this extra vba line.

But there may be other ways to do this, and I always like seeing different methods from everyone in the Lounge

zeddy

Reply | Quote

A fascinating thread ! Thanks for all the insights it has generated.

It is very odd, isn’t it, that you can type days, months and years directly into a “Date/time” formatted cell in Excel without having to type the colons but – as far as I can see, there’s no equivalent way to type directly into the hours and minutes (or seconds) fields.

Other than typing CTRL SHIFT ; to record the current time, that is.

Even odder, if you try typing directly into a Time formatted cell, the typed digits still end up in the date part of the stored number.

Reply | Quote

Dear All:
Science says time is infinite, but in Excel there are only 1,440 minutes in a day. Based on that principal see the attached

I am sure others can further simplify this even more, but I do know this one works.

If you need to copy the results remember to the Copy Values rather than the formulas.

Hope this helps

DuthieT

Reply | Quote

duthiet

Albert Einstein said
“Only two things are infinite: the universe and human stupidity.
And I’m not sure about the universe”

Reply | Quote

Zeddy,

Ok, you got me stumped! What does this line of code do?

If I enter 1377 it jumps right over this line of code and presents my message and leaves the cursor at the next line.

If I enter a text value like ‘K’ it errors out.

What am I missing?

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

When you enter 1377, then, just as before, your error trapping kicks in, i.e. 1377 is still NOT allowed because 77 minutes is not allowed.

Now, enter 1230 into say, [A4]
It will be ‘converted’ and display as 12:30 in the cell.
In the formula bar, it will show as 12:30:00 i.e. will show the contents in te formula bar as hh:mm:ss

Now, in the formula bar, change this 30 to 35
The cell will now display 12:35
No error message is displayed.

..but, if you tried this edit with your posted file, it will trigger your error message.

So, the line of code I put in now allows you to re-enter say, a new four-digit valid number e.g. 1145 into an existing time-cell, or edit the contents in the formula bar, without triggering the error message.

zeddy

Reply | Quote

Hi RG

..I was thinking about allowing text entries so that instead of a time, the User could enter something like “holiday”, or “sick”, “fired’, “sacked’ ,
..maybe “promoted” even

..but that’s something else.

zeddy

Reply | Quote

Hi RG

..so another fix is still required to deal with ‘text’ input etc etc etc
..so my update wasn’t a ‘six’ after all.
..not even a ‘four’
..umpires call

zeddy

Reply | Quote

Hi RG

..and I meant to say nice to see you using
If WorksheetFunction.IsNumber(Target) Then

..rather than VBA’s
If IsNumeric(Target)

Have you ever seen this example:

Code:

Sub test()

zVar = "(123,,,3.4,5,,45E67)"

If IsNumeric(zVar) Then
MsgBox zVar & " is numeric"
Else
MsgBox "not numeric"
End If

End Sub

zeddy

Reply | Quote

Zeddy,

Yep I’ve seen it and it would be useful here! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Brutal doesn’t cover it.

cheers, Paul

Reply | Quote

zeddy, I want some of what you’re on!

cheers, Paul

Reply | Quote