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?

=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" wrote in message
...
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?

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" wrote:

=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" wrote in message
...
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?

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" wrote in message
...
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" wrote:


=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" wrote in message
...
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?

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" wrote:

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" wrote in message
...
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" wrote:


=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" wrote in message
...
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?

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

--

HTH

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


"Karthik" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:



=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" wrote in message
...
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?

Thanks a ton, Bob

"Bob Phillips" wrote:

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

--

HTH

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


"Karthik" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:



=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" wrote in message
...
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?

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.

"Karthik" wrote:

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?