Workingday Calculations

K

Karthik

I am aware of the NETWORKDAYS function in EXCEL for TAT (Turn Around Time)
Calculations between days excluding weekends and holidays. However, I want to
know if there is a way of doing the same calculation including Saturdays only
since it is considered as a workday for my calculation purposes.

Is there any way this can be done with a formula available in Excel 2002?
 
B

Bob Phillips

=SUMPRODUCT(INT((end_date-WEEKDAY(start_date+1-{2;3;4;5;6;7})-start_date+8)/
7))-
SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6;7},0))*(holidays>=sta
rt_date)*(holidays<=end_date))


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Karthik

Thanks a lot Bob. One question though - in some cases the formula result
shows an extra day as compared to a straight calculation (end_date -
start_date). Could you please explain?

Another question - If I wanted to calculate the TAT including all days
between start and end date (including the weekends) and exclude only certain
specific holidays, is there a way this could be done?
 
B

Bob Phillips

Kathrik,

It emulates NETWORKDAYS, so it includes both the start and end date, not the
difference between them. Just subtract one if you want it the other way.

It already handles holidays. Build a list of holiday dates, and name it
holidays.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Karthik

Thanks Bob for the really quick response. I already made the list of holidays
and fed it to the formula. If I understand correctly, all I need to do is to
subtract one from the formula resut to get the TAT minus the holidays
specified only.

For example, if start date is 1st Nov and end date is 6th, with 3rd being a
holiday, I need the formula to return the answer as 4 days (Only 3rd being
excluded, all other days should be included)

Can you let me know?
 
B

Bob Phillips

Yes, just add a -1 at the end of the formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Karthik

Thanks a ton, Bob

Bob Phillips said:
Yes, just add a -1 at the end of the formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

top.jimmy

Try this:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&EOMONTH(TODAY(),0))),2)<7),--(ISNA(MATCH(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&EOMONTH(TODAY(),0))),X25:X32,0))))

Where X25:X32 would be the Holidays for the year.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top