# Formula Duration in Hours

• 0
• 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.
• 112 Points Posted 1 year ago

• 0
• 350 Points 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]))
• 112 Points 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

dwhawe, Champion

• 908 Points 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
• 112 Points That did it. Thanks to all who responded!