A function for custom 'Time'

[Oliver Dewar]

Hi all!

I've never created a function before but find myself in need of one.

In vba, if you put in

Range("A1").value = Time

then you get a result such as this: 10:30:22 am

This is too specific for my needs.

I'd like to create a function called Time2 where it returns the same result as above but without the seconds... so

Range("A1").value = Time2

Returns: 10:30 am

Does anyone know how to write this function?

 

[Peter_SSs]

Firstly, could you just format the cell as "h:mm AM/PM"?

Secondly, if you do want to actually remove any seconds, do you want to round up to the next minute if the seconds are >=30 or just truncate the seconds?

One of these may suit you

'This truncates the seconds
Range("A1").Value = TimeSerial(Hour(Time), Minute(Time), 0)

'This rounds the seconds
Range("B1").Value = TimeSerial(Hour(Time), Minute(Time) + IIf(Second(Time) < 30, 0, 1), 0)

 

[Toonies]

Hi this may help

[LEFT]Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub[/LEFT]
 
 
 
 


</PRE><!-- END TEMPLATE: bbcode_html -->

check it out at

http://www.cpearson.com/excel/datetimeentry.htm

 

[Oliver Dewar]

Fantastic!

Thanks so much for all your help on this!

Oliver