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!
 476 Points
Posted 3 years ago
Xavier Fan, Champion
 410 Points
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://quickbasecommunity.intuit.com/questions/1225661calculatedurationexcludingweekendsdepen...
(The [Deadline] in that question would be your EndDateTime here).
In the answer  the FormulaNumeric 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])
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://quickbasecommunity.intuit.com/questions/1225661calculatedurationexcludingweekendsdepen...
(The [Deadline] in that question would be your EndDateTime here).
In the answer  the FormulaNumeric 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])
 476 Points
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.
Also, is it possible to convert a date field and a time field into a single date/time field?
Thanks again.
Xavier Fan, Champion
 410 Points
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.
Related Categories

Formulas & functions
 2924 Conversations
 74 Followers

Tables & fields
 7209 Conversations
 182 Followers