Duration in hours between 2 dates and times

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

How do we calculate the duration in hours for a job that extends over 2 or more work days?


Example:

Start Date: 10/10/2010
Start Time: 10 PM

End Date: 10/11/2010
End Time: 4 AM

Thanks in advance!

Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
Assuming that you have two Date/Time fields: [StartDateTime] and [EndDateTime].

If this will never fall on a weekend, you can create a Formula - Duration field, and for the formula, simply subtract EndDateTime - StartDateTime to get a Duration (e.g. the number of hours).


If there are weekends in between, you will additionally need to subtract out the number of hours in the weekend.

See the similar question and answer here:

https://quickbase-community.intuit.com/questions/1225661-calculate-duration-excluding-weekends-depen...


(The [Deadline] in that question would be your EndDateTime here).

In the answer - the Formula-Numeric field would give you the number of hours.  You can convert that to a Duration if you want.

====

EDIT:  Here is how you combine a Date and Time field to get a Date/Time field:

[Start Date] - Date field

[Start Time] - Time of Day field

[Start Date/Time] - Formula - Date/Time field, with formula:  ToTimestamp([Start Date], [Start Time])
Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb
Thank you for your quick response! Currently, the date and time are actually captured separately. How would your proposed solution be accomplished in this case?

Also, is it possible to convert a date field and a time field into a single date/time field?

Thanks again.
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
I've edited the answer to add how to combine a Date field, and a Time of Day field, into a single Date/Time field.
Photo of Juan

Juan

  • 480 Points 250 badge 2x thumb
Thank you Xavier!
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
You're welcome!