Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Add days, excluding Sunday's and Holiday's

I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,104
Default Add days, excluding Sunday's and Holiday's

Have a look at the WORKDAY function
Example A1 holds today's date (July 8), B1 holds number 5
WORKDAY(A1,B1) returns July 13 (Wed to Wed)
You may need to format the cell holding the formula if it returns a numbers
like 40009
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JoeL" wrote in message
...
I have a process that requires 7 days to complete. So, I've been asked to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Add days, excluding Sunday's and Holiday's

Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default Add days, excluding Sunday's and Holiday's

Hi,

I think this is a lot more complicated than a simple WORKDAY function,
because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part is
easy.

If the process is only 7 days or less then

=WORKDAY(A1,A2+1,C1:C5)

If not ...

Also, the WORKDAY function is an ATP function so in 2003 or earlier you will
need to attach it - Tools, Add-ins, check Analysis ToolPak.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Add days, excluding Sunday's and Holiday's

Hi Mike,

What's "Days" referencing?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Add days, excluding Sunday's and Holiday's

What you could do is use Chip Pearson's betternetworkdays Function and use
Goal Seek to get 7 as the number of workdays
Look he

http://www.cpearson.com/excel/betternetworkdays.aspx


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Shane Devenshire" wrote in
message ...
Hi,

I think this is a lot more complicated than a simple WORKDAY function,
because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part
is
easy.

If the process is only 7 days or less then

=WORKDAY(A1,A2+1,C1:C5)

If not ...

Also, the WORKDAY function is an ATP function so in 2003 or earlier you
will
need to attach it - Tools, Add-ins, check Analysis ToolPak.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Add days, excluding Sunday's and Holiday's

Niek,

I messed up days is a named range containg the days to add, I changed it in
the first part of the formula to 7 (the days to add) but forgot in the second
but because I had that named range in my sheet it worked for me. It should be

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7))

Since then i've been working on a shorter version but cant get it into 1
cell (yet)

A1 =start date
A2 = =WORKDAY(A1,7,Holidays)
A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

It works but as I said can't get it into a single cell

Mike

Mike
"Niek Otten" wrote:

Hi Mike,

What's "Days" referencing?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Add days, excluding Sunday's and Holiday's

This is great, but it doesn't include Saturdays. Can we modify it to include
this day too?

"Bernard Liengme" wrote:

Have a look at the WORKDAY function
Example A1 holds today's date (July 8), B1 holds number 5
WORKDAY(A1,B1) returns July 13 (Wed to Wed)
You may need to format the cell holding the formula if it returns a numbers
like 40009
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JoeL" wrote in message
...
I have a process that requires 7 days to complete. So, I've been asked to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!




  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Add days, excluding Sunday's and Holiday's

Wow, this is extensive. I didn't get it to work for me though. I changed the
A1's to C7 (input date) and created a list of holidays (named Holidays and
formatted to date). However, I keep getting the #NUM! error.

"Mike H" wrote:

Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Add days, excluding Sunday's and Holiday's

This didn't work for me. It is only 7 days or less, but what are you assuming
is in cells A1, A2 and C1 through C5? Can you break this down for me and
maybe I can piece together? Thanks.

"Shane Devenshire" wrote:

Hi,

I think this is a lot more complicated than a simple WORKDAY function,
because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part is
easy.

If the process is only 7 days or less then

=WORKDAY(A1,A2+1,C1:C5)

If not ...

Also, the WORKDAY function is an ATP function so in 2003 or earlier you will
need to attach it - Tools, Add-ins, check Analysis ToolPak.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Add days, excluding Sunday's and Holiday's

Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Mike H" wrote in message
...
Niek,

I messed up days is a named range containg the days to add, I changed it
in
the first part of the formula to 7 (the days to add) but forgot in the
second
but because I had that named range in my sheet it worked for me. It should
be

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7))

Since then i've been working on a shorter version but cant get it into 1
cell (yet)

A1 =start date
A2 = =WORKDAY(A1,7,Holidays)
A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

It works but as I said can't get it into a single cell

Mike

Mike
"Niek Otten" wrote:

Hi Mike,

What's "Days" referencing?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays
you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked
to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!



  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Add days, excluding Sunday's and Holiday's

I assumed you would know that

"Niek Otten" wrote:

Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Mike H" wrote in message
...
Niek,

I messed up days is a named range containg the days to add, I changed it
in
the first part of the formula to 7 (the days to add) but forgot in the
second
but because I had that named range in my sheet it worked for me. It should
be

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7))

Since then i've been working on a shorter version but cant get it into 1
cell (yet)

A1 =start date
A2 = =WORKDAY(A1,7,Holidays)
A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

It works but as I said can't get it into a single cell

Mike

Mike
"Niek Otten" wrote:

Hi Mike,

What's "Days" referencing?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays
you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked
to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!


  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Add days, excluding Sunday's and Holiday's

Instead of ENTER, use CTRL+SHIFT+ENTER to commit the formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JoeL" wrote in message
...
Wow, this is extensive. I didn't get it to work for me though. I changed
the
A1's to C7 (input date) and created a list of holidays (named Holidays and
formatted to date). However, I keep getting the #NUM! error.

"Mike H" wrote:

Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked
to add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Add days, excluding Sunday's and Holiday's

I didn't know that... but now this formula works great. Thank you!

"Mike H" wrote:

I assumed you would know that

"Niek Otten" wrote:

Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Mike H" wrote in message
...
Niek,

I messed up days is a named range containg the days to add, I changed it
in
the first part of the formula to 7 (the days to add) but forgot in the
second
but because I had that named range in my sheet it worked for me. It should
be

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7))

Since then i've been working on a shorter version but cant get it into 1
cell (yet)

A1 =start date
A2 = =WORKDAY(A1,7,Holidays)
A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

It works but as I said can't get it into a single cell

Mike

Mike
"Niek Otten" wrote:

Hi Mike,

What's "Days" referencing?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays
you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked
to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!


  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Add days, excluding Sunday's and Holiday's

Your welcome and I should have pointed out it was an array

"JoeL" wrote:

I didn't know that... but now this formula works great. Thank you!

"Mike H" wrote:

I assumed you would know that

"Niek Otten" wrote:

Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Mike H" wrote in message
...
Niek,

I messed up days is a named range containg the days to add, I changed it
in
the first part of the formula to 7 (the days to add) but forgot in the
second
but because I had that named range in my sheet it worked for me. It should
be

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10 ))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),AB S(7))

Since then i've been working on a shorter version but cant get it into 1
cell (yet)

A1 =start date
A2 = =WORKDAY(A1,7,Holidays)
A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

It works but as I said can't get it into a single cell

Mike

Mike
"Niek Otten" wrote:

Hi Mike,

What's "Days" referencing?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDI RECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days) *10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))) ,ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays
you
want to excelude. Format as date.

Mike

"JoeL" wrote:

I have a process that requires 7 days to complete. So, I've been asked
to
add
7 days to a manual inputted date (cell c1), to include Saturday's, but
not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting # of days between 2 dates excluding Fri & Sat) Khaledity Excel Worksheet Functions 33 March 29th 09 01:05 PM
Excluding leap days in a formula PMBO Excel Discussion (Misc queries) 7 December 11th 08 08:03 PM
Working Days excluding Sundays danh Excel Discussion (Misc queries) 6 March 31st 07 08:52 PM
Due date excluding weekend days Jfilbig Excel Worksheet Functions 5 February 5th 06 07:45 PM
Count Days excluding Sundays KENNY Excel Worksheet Functions 3 November 11th 04 06:26 PM


All times are GMT +1. The time now is 11:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"