CODEWITHSUNDEEP
vbaexcel
Sam
Sam

Reputation: 7313

How do I format Time in [h]:mm excel format using format() function in VBA

Is it possible to format time in [h]:mm format using VBA?

[h]:mm format in excel would show 25 hours as 25:00, 125 hours as 125:00

I've tried several things such as:

format(datetime, "[h]:mm")
format(datetime, "H:mm")
format(datetime, "hh:mm")

None of these have the desired effect. I've also had a look through the MS help and can't find anything useful.

Upvotes: 10

Views: 48149

Answers (4)

Joel Spolsky
Joel Spolsky

Reputation: 33697

Use the TEXT worksheet function via the application object, as so:

  x = Application.Text(.294,"[h]:mm")

Upvotes: 17

Tim Williams
Tim Williams

Reputation: 166850

JFC beat me to it, but here's what I came up with anyway...

Sub Test()
    Debug.Print FormatHM(24 / 24)       '24:00
    Debug.Print FormatHM(25 / 24)       '25:00
    Debug.Print FormatHM(48 / 24)       '48:00
    Debug.Print FormatHM(48.6 / 24) '48:36
End Sub

Function FormatHM(v As Double) As String
    FormatHM = Format(Application.Floor(v * 24, 1), "00") & _
                 ":" & Format((v * 1440) Mod 60, "00")

End Function

Upvotes: 4

Jean-François Corbett
Jean-François Corbett

Reputation: 38550

It looks like VBA's Format function has no built-in way of doing this. (Eyes rolling.)

This home-made function will do the trick:

Function HoursAndMinutes(datetime As Date) As String
    Dim hours As Integer
    Dim minutes As Integer
    hours = Int(datetime) * 24 + Hour(datetime) ' the unit of Date type is 1 day
    minutes = Round((datetime * 24 - hours) * 60)
    HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

Usage:

    Dim datetime As Date
    datetime = TimeSerial(125, 9, 0) ' 125 hours and 9 minutes
    Debug.Print HoursAndMinutes(datetime) ' 125:09

Upvotes: 1

SeanC
SeanC

Reputation: 15923

not with the format function, but you can use Range(MyData).NumberFormat = "[h]:mm"

Upvotes: 1

Related Questions