Forum Discussion

MarkSylvers's avatar
MarkSylvers
Qrew Trainee
8 years ago

Formula Duration in Hours

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.
  • LisaSell1's avatar
    LisaSell1
    Qrew Trainee
    8 years ago
    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]))
    • MarkSylvers's avatar
      MarkSylvers
      Qrew Trainee
      8 years ago
      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
    • DavidHawe's avatar
      DavidHawe
      Qrew Trainee
      8 years ago
      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