Entering time

  • Thread starter Thread starter Jim Lavery
  • Start date Start date
J

Jim Lavery

Can anyone tell me how....If you enter 1.00 it can be converted to a time
format automatically 1:00.
Also if you want to add hours such as 10:00 plus 10:00 plus 10:00 it reads
30:00 instead of 06:00 (24 hour format)
 
One way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub

Since your example was somewhat ambigous, this assumes that you're
using the integer for hours and the two decimal places for minutes,
rather than hundredths of an hour.

Note the [h]:mm number format, which will not roll over hours over 24
 
Excellent, it works great


J.E. McGimpsey said:
One way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub

Since your example was somewhat ambigous, this assumes that you're
using the integer for hours and the two decimal places for minutes,
rather than hundredths of an hour.

Note the [h]:mm number format, which will not roll over hours over 24


Jim Lavery said:
Can anyone tell me how....If you enter 1.00 it can be converted to a time
format automatically 1:00.
Also if you want to add hours such as 10:00 plus 10:00 plus 10:00 it reads
30:00 instead of 06:00 (24 hour format)
Click to expand...
Click to expand...
 
Mr M.
Your code is indeed excellent.
My VB knowledge is next to zilch so I hope you can indulge me just
once, I have several similar sheets which each month I cut and paste a
column of data comprising days (rows 2 to 25) followed by time data in
the format as the previous poster.
Since cut and paste will not fire up the Worksheet_Change Event is
there a way of triggering a macro that will do this change and then
continue to repeat for the columns in other sheets?
Your help is appreciated.

Thanks






J.E. McGimpsey said:
One way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub

Since your example was somewhat ambigous, this assumes that you're
using the integer for hours and the two decimal places for minutes,
rather than hundredths of an hour.

Note the [h]:mm number format, which will not roll over hours over 24


Jim Lavery said:
Can anyone tell me how....If you enter 1.00 it can be converted to a time
format automatically 1:00.
Also if you want to add hours such as 10:00 plus 10:00 plus 10:00 it reads
30:00 instead of 06:00 (24 hour format)
Click to expand...
Click to expand...
 
Hi people
Just thought I'd bring this up again as a reply hasn't been forthcoming.
Could someone start me off by showing how I could utilise this code in a macro?
Thanks for your time(no pun intended).

Angelo





Mr M.
Your code is indeed excelent.
My VB knowledge is next to zilch so I hope you can indulge me just
once, I have several similar sheets which each month I cut and paste a
column of data comprising days (rows 2 to 25) followed by time data in
the format as the previous poster.
Since cut and paste will not fire up the Worksheet_Change Event is
there a way of triggering a macro that will do this change and then
continue to repeat for the columns in other sheets?
Your help is appreciated.

Thanks






J.E. McGimpsey said:
One way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub

Since your example was somewhat ambigous, this assumes that you're
using the integer for hours and the two decimal places for minutes,
rather than hundredths of an hour.

Note the [h]:mm number format, which will not roll over hours over 24


Jim Lavery said:
Can anyone tell me how....If you enter 1.00 it can be converted to a time
format automatically 1:00.
Also if you want to add hours such as 10:00 plus 10:00 plus 10:00 it reads
30:00 instead of 06:00 (24 hour format)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top