Exclude Sunday and Pub holiday

Hi I had this in the Functions sections, but after lots of views no one seems to know if this doable. So i was hoping some coding person could help as i have no idea of how to write code



Hi I have a calculation, several in fact and basically they check when a test comes in and when it is released. It's measured over 24 hrs, 48 hrs ,72hrs and 72+ hrs.
I use this to work out the interval
=(D2+E2)-(B2+C2)

This is what pulls the number into the time period
=COUNTIFS('Raw data'!F2:F1667,">01:00",'Raw data'!F2:F1667,"<=24:00")

So where my problem is that no-one works Sunday or Public holidays, so if a case comes in Friday after noon goes monday afternoon showing as 72 hrs where in effect its should be showing as 48 hrs.
Is there away that case that run over a Sunday or pub holiday take those hours of?

I have attached the file just in case you more clarification.

Any help is really appreciated, i am right under the pump now/

Hi Frazzle6

quick question, where does the raw data come from and how does it get into the "Raw Data" tab please (is it possible to alter the format of the cells for example?)
JMAC

I use SQL developer to pull the data then export into excel. I rename the tab to Raw data.

can you pull the date & time as one field rather than two separate fields for each of start & finish

I'm not sure does it make a huge diffrerence?, i can have a go at it

wait a bit, i am having a play with the test data now, making some progress (I think)

excellent if you can help, tried joining the fields, crashed the query

OK,

if the:

start date = 23/12/2013
start time = 23:36

finish date = 27/12/2013
finish time = 23:43

what are you expecting to see as the result (Australian public holidays used 25 & 26/12)

Jmac

I would like to that to say the interval is 48:07.

TAT Hist2.xlsx

I hate time and date "stuff" in excel, I never seem to be able to get it to do what I want

take a look at the attached. I have attempted to break the problem down to simple steps using helper cells and I can see the right answer... if only I could get the minutes < 10 to be two digits, can change the display format but not it seems the concatenate function

workdays seems to include the starting date hence the -1 in the formula

very close, let me know how you go

jmac

Hi Guys,

This one has had me scratching my head.

I have added some helper columns to avoid a monster formula

Col H:I are for interest

Column J uses NETWORKDAYS, and I have put the Public Holidays for NSW on a separate sheet.
Column M is my attempt to calculate the days to take off. Effectively Public Holidays and Sundays.

The problem with the NETWORKDAYS formula is that it doesn't count Saturday as a workday. Therefore we have to add back Saturdays. I have attempted to do that in column L.

The next problem is that I was expecting there to be no Sundays in your data, because you don't work on Sunday. Wrong! There are quite a few, and I don't know how to treat them or get around this issue.

Anyway

Let me know how you progress.


Frazzle6 - TAT Histo.xlsx

Regards, David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

hi David,

networkdays.intl actually allows you specifY saturday as a workday

I havent checked out kvsrinivasamurthy latest post yet but on first impressions it assumes sat/sun as non working days... need to check the code further

like where kvsrinivasamurthy is heading however

Hi Guys, thanks so much for all your help, really do appreciate it. A developer from the UK came through with coding that does the trick.