Formula Duration in Hours

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I am importing a .csv file with two date columns. The date columns are formatted
Date & Time. 


I need a formula that calculates the number of hours between the two date / time columns but doesn't take into account the minutes. All minutes weather .01 or .59 should be dropped from the formula calculation.

Two examples:

BegDate                 EndDate                       Hours

1/3/2018  8:01       1/13/2018  11:30           243
1/4/2018  8:01       1/15/18  18:15               274

Note: The Date / Time appear in the cells as military time but in the excel Formula bar as AM or PM. Thought you might need to know.

Thanks for any help.
Photo of Mark Sylvers

Mark Sylvers

  • 112 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Lisa

Lisa

  • 350 Points 250 badge 2x thumb
You could make 2 fields -
Formula Duration type named  [Date Time Duration] = [End DateTime]-[Start DateTime]
then a Formula Numeric Type named [Hours] = Floor(ToHours([Date Time Duration]))
Photo of Mark Sylvers

Mark Sylvers

  • 112 Points 100 badge 2x thumb
The result from the two formuls are working but then noticed the following:
The two records below should both calculate to 100 hours but the first one does not.

BegDate                                EndDate                   Hours
01-13-2018 08:01         01-17-2018 12:00        99 (should be 100 hrs)
01-13-2018 08:01         01-17-2018 12:01       100
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
I believe ToHours returns a whole number that is not rounded so the result is correct based on what it is being asked and also remove Floor as Floor alone may be the problem.  Try changing:

Formula Numeric Type named [Hours] = Floor(ToHours([Date Time Duration]))

To:

Formula Numeric Type named [Hours] = ToMinutes([Date Time Duration])/60
Photo of Mark Sylvers

Mark Sylvers

  • 112 Points 100 badge 2x thumb
That did it. Thanks to all who responded!