Difference between 2 dates - excluding working hours, weekends and holidays

Hi ray2 and welcome to the forum.

BTW - I think you meant 9AM when presenting your problem.

With A1 = Ticket Open date and Time and
with B1 = Ticket Closed date and time (make that 9am and not 9pm)
in C1 put this formula for the total number of hours.

Please Login or Register  to view this content.

So here is the reasoning.
Ticket opens during business hours. So hours on Open Date = 5pm - open time or

Please Login or Register  to view this content.

Similarly hours on Closed Ticket day is when it closed - start of work or 8:30 (8.5)

Please Login or Register  to view this content.

Then we use NetWorkDays between the two dates but subtract two since we used them above.
We also throw in the holiday, in text format or

Please Login or Register  to view this content.

But we have to multiply the full number of workdays by work hours or 8.5 hours a day.

These three parts roll into the single equation above in C3.

You would have to change the holidays or hours worked per day for different problems

hope this does it for you.

Below formulas are c/o resident date guru Daddylonglegs:

Please Login or Register  to view this content.

References are as follows:

A2: start datetime
B2: end datetime
H1:H10 list of public holiday dates

@DO (and @DLL?)
I have to live up to my signature and do a test.

What did I do wrong?

Ok - I got my money messed up in the wrong cells. Now it looks like it works in second attach.

@ray2, I should have added that you should format the resulting cell with custom format of: [h]:mm
(else * 24 and format as number if you prefer decimal hours)

These calculations are often complex and invariably daddylonglegs has resolved similar (if not identical) problems at some point in the past ... as robustly / elegantly as is possible with native functions (as illustrated here).

*** Disregard, after some modifications and separation, I reused DonkeyOte's formula without error. Thank you!! UE can be a bear.

I tried to modify the formulas from MarvinP and DonkeyOte to no avail.

I have two dates (1/11/11 5:11 PM and 1/12/11 11:08 AM) and I have a workday of 8am to 6pm and I need to determine the number of work hours between them. Subtracting potential holidays would be a bonus. When I make the changes to the formulas posted (my cells are P24 and R24) I get inconsistent formula and incorrect values like 1/0/00 3:57:00 or 55:37 hours when I should get 3:57. Perhaps I'm substituting something incorrectly, but it seems fairly straightforward, so its most likely "UE."

Any help would be greatly appreciated!

Regards,
Jimmy

Originally Posted by jimmy37

*** Disregard, after some modifications and separation, I reused DonkeyOte's formula without error. Thank you!!

That's good news ... going forward please do not ask questions in the thread of another member - create your own and post a link back to the original for reference.

Sajid45,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

saurabhlotankar,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.