Turn around times based on days and hours worked

[smllchng5]

So I have an exel spreadsheet that I have been asked to see if I could come up with a formula to figure out how many hours to took to complete a task. So we have set hours of 8am-5pm PST and we want to calculate how many hours it took to complete a task. I can figure out how to do it if it is within the same day, but how do I figure it out if it is done the next business day. (See example below..the second line....was recieved on Friday 9/13, but not completed until Monday 9/16 at 8:30am. How can I write a formula that will calculate how many hours it took to complete the task and put that in column O? Also, what if there is a holiday...how do I indicate that (Like on the 3rd line...9/2 was a holiday) ? Thank you for your assistance

 

[ranman256]

date/time can be in 1 field. it is still a date and then elapsed time can be calculated

cell D2 = 9/13/24 9:00 am

J2-D2

no need for 2 fields.

 

[smllchng5]

So sadly I don't know that I can change those cells from 2 to 1, as it is not created by me or my team. But even so...that answers how to fix the first row of data...but I must not have been clear on the other two rows and what I need. So we are trying to figure out how many hours it took them to complete the task. If over a week-end or a holiday is involved, how can I write a formula for that. (So if they started at 3:00 pm on Friday and complted at 8:30 am on Monday (Business day is 8am - 5pm) ...I want it to show that it took 2.5 hours to complete) Also...what if there is a holiday in there as well. Thanks in advance for your help!!!