Excel (2010) Calculate Working Hours Between DateCreated and DateAssigned
I have found several posted solutions(?) but none seem to work correctly or return what I need.
What I am needing to calculate are the working hours between a Date/Time of item creation and the Date/Time of item assignment.
The working hours are between 08:00 and 17:00, Monday through Friday. The formula needs to disregard weekend days/hours and weekday non-working hours.
Additionally it needs to account for Date/Time of item creation and Date/Time of item assignment that occurs outside of the hours of 08:00 and 17:00. (EXAMPLE: if the item was created at 08/02/10 06:15 it should be assumed/calculated as if it was created at 08/02/10 08:00; if the item was assigned at 08/02/10 19:24 is should be assumed/calculated as if it was assigned at 08/02/10 17:00 or 08/03/10 08:00 - whichever is easier or makes the most sense.)
Lastly, the results may be greater that 24 hours - this has also been problematic. I need to be able to display and perform additional functions (e.g. AVERAGE, MIN, MAX) on the cumulative results. Results greater that 24 hours seem to throw a wrench into things.
The attached/uploaded Excel (2010) file has the requisite data. Excel function(s) preferred over VBA.
Thank you for any assistance! Time-to-Assignment.xlsx
What I am needing to calculate are the working hours between a Date/Time of item creation and the Date/Time of item assignment.
The working hours are between 08:00 and 17:00, Monday through Friday. The formula needs to disregard weekend days/hours and weekday non-working hours.
Additionally it needs to account for Date/Time of item creation and Date/Time of item assignment that occurs outside of the hours of 08:00 and 17:00. (EXAMPLE: if the item was created at 08/02/10 06:15 it should be assumed/calculated as if it was created at 08/02/10 08:00; if the item was assigned at 08/02/10 19:24 is should be assumed/calculated as if it was assigned at 08/02/10 17:00 or 08/03/10 08:00 - whichever is easier or makes the most sense.)
Lastly, the results may be greater that 24 hours - this has also been problematic. I need to be able to display and perform additional functions (e.g. AVERAGE, MIN, MAX) on the cumulative results. Results greater that 24 hours seem to throw a wrench into things.
The attached/uploaded Excel (2010) file has the requisite data. Excel function(s) preferred over VBA.
Thank you for any assistance! Time-to-Assignment.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
barryhoudini, that worked perfectly - thank you!