CODEWITHSUNDEEP
vbaexcel
Bruno Repetto
Bruno Repetto

Reputation: 11

How do I format the difference of two dates as Number of days, hours, minutes in VBA?

I have a problem related to this question. In that post, formula-based suggestions were made. Unfortunately, those will not work for what I need:

I have a pivot table that contains fractional days, ie. 35.39375 days or 28.32361111 days. I wish to format those numbers as 35 09:27 and 28 07:46, respectively, (in #days, hours, minutes). As well, I'd like to format their row and column totals similarly. And I need to do it in VBA.

Setting the pivot table cell formatting to .NumberFormat = "d hh:mm" does not work as the number of days will be truncated, since they will generally exceed 31 days.

I am using EXCEL 2007.

ADDENDUM:

I thank all people who have suggested formula-based approaches. Those do not work when specifying number formats within VBA for a pivot table. For instance, the following code is faulty because it will cause the numbers of days that exceed 31 to be truncated. The nature of the .NumberFormat specification does not allow formula-based formats either (at least, not that I can see):

With ActiveSheet.PivotTables("Durations")
      .
      .
      .
    .AddDataField .PivotFields("Duration"), "Count of Duration", xlCount
    With .PivotFields("Count of Duration")
        .Caption = "Sum of Duration"
        .Function = xlSum
        .NumberFormat = "d hh:mm"  <<===  This will cause the number of days to be truncated.
    End With
      .
      .
      .
With

Upvotes: 1

Views: 249

Answers (1)

Comintern
Comintern

Reputation: 22185

Date types in Excel (and VBA) are already stored as days.[fractional days]. All you need to do is format it.

VBA:

foo = 35.39375
Debug.Print Int(foo) & " " & Format$(foo - Int(foo), "hh:nn") 'Prints 35 09:27

Formula:

=CONCATENATE(INT(A1), TEXT(A1, " hh:mm"))

Upvotes: 1

Related Questions