Workingday Calculations

[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?

 

[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)

 

[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?

 

[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)

 

[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?

 

[Bob Phillips]

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

--

HTH

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

 

[Karthik]

Thanks a ton, Bob

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

--

HTH

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

 

[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.