Discussions

Expand all | Collapse all

Formula Duration in Hours

  • 1.  Formula Duration in Hours

    Posted 03-07-2018 17:17
    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.


  • 2.  RE: Formula Duration in Hours

    Posted 03-07-2018 21:05
    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]))


  • 3.  RE: Formula Duration in Hours

    Posted 03-07-2018 22:00
    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


  • 4.  RE: Formula Duration in Hours

    Posted 03-07-2018 23:45
    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


  • 5.  RE: Formula Duration in Hours

    Posted 03-08-2018 00:12
    That did it. Thanks to all who responded!