Combining today +1 and weekday function

Hello,
I create sheets that are used the next day. I would like to find a way to
calculate tomorrow's date, Monday-Fri, with no holidays included. I haven't
found a way to make it work yet.

i.e:

today()+1 - weekend, holidays

Thank you for your advice!

Monique

On Tue, 8 Mar 2005 09:39:13 -0800, "Monique"
<[email protected]> wrote:

>Hello,
>I create sheets that are used the next day. I would like to find a way to
>calculate tomorrow's date, Monday-Fri, with no holidays included. I haven't
>found a way to make it work yet.
>
>i.e:
>
>today()+1 - weekend, holidays
>
>Thank you for your advice!
>
>Monique

Take a look at the WORKDAY function:

=WORKDAY(TODAY(),1,Holidays)




--ron

I am not sure how this expression is suppose to be written. I tried it all
the ways I could think of; including copying your example, but I haven't
found the solution yet.

"Ron Rosenfeld" wrote:

> On Tue, 8 Mar 2005 09:39:13 -0800, "Monique"
> <[email protected]> wrote:
>
> >Hello,
> >I create sheets that are used the next day. I would like to find a way to
> >calculate tomorrow's date, Monday-Fri, with no holidays included. I haven't
> >found a way to make it work yet.
> >
> >i.e:
> >
> >today()+1 - weekend, holidays
> >
> >Thank you for your advice!
> >
> >Monique
>
> Take a look at the WORKDAY function:
>
> =WORKDAY(TODAY(),1,Holidays)
>
>
>
>
> --ron
>

On Tue, 8 Mar 2005 17:03:01 -0800, "Monique"
<[email protected]> wrote:

>I am not sure how this expression is suppose to be written. I tried it all
>the ways I could think of; including copying your example, but I haven't
>found the solution yet.

I don't know how to respond helpfully to your statement.

But perhaps if you convey exactly what you wrote, and exactly what happened,
and exactly what you expect to happen, I might be able to respond.

Did you check HELP for the function?



--ron

Hi Ron,
I am new to Excel and unfamiliar with the order and syntax of functions and
formulas. I am using my speadsheet to learn about Excel functions, and to
automate one of my tasks at work. I am not sure how to include holidays
since I do not have all of them off. I

I did find a formula that will work, however, it does not include holidays.
I tried to put in the holiday function. The dialog box said I had too many
arguments.
i.e.
=IF(WEEKDAY(TODAY())>5,TODAY()-WEEKDAY(TODAY(),3)+7,TODAY()+1)

My intention is to automate the date of my spreadsheet so that it updates
itself to display the next day's date, within a five day work week, minus
holidays.

I did use the help files. It said, "you need help".



"Ron Rosenfeld" wrote:

> On Tue, 8 Mar 2005 17:03:01 -0800, "Monique"
> <[email protected]> wrote:
>
> >I am not sure how this expression is suppose to be written. I tried it all
> >the ways I could think of; including copying your example, but I haven't
> >found the solution yet.
>
> I don't know how to respond helpfully to your statement.
>
> But perhaps if you convey exactly what you wrote, and exactly what happened,
> and exactly what you expect to happen, I might be able to respond.
>
> Did you check HELP for the function?
>
>
>
> --ron
>

On Tue, 8 Mar 2005 22:33:03 -0800, "Monique"
<[email protected]> wrote:

>Hi Ron,
>I am new to Excel and unfamiliar with the order and syntax of functions and
>formulas. I am using my speadsheet to learn about Excel functions, and to
>automate one of my tasks at work. I am not sure how to include holidays
>since I do not have all of them off. I
>
>I did find a formula that will work, however, it does not include holidays.
>I tried to put in the holiday function. The dialog box said I had too many
>arguments.

From HELP:

Holidays is an optional list of one or more dates ...

==================

What that means is that in some contiguous range, you place a list of the dates
which represent holidays. For example:

D1: 25 Mar 2005
D2: 30 May 2005
D3: 4 Jul 2005
..
..
..
D7: 24 Dec 2005
D8: 25 Dec 2005


You then insert the range reference (D1:D8) into the formula I posted in place
of the word "Holidays".

OR, you can name this range Holidays. On the main menu bar:
Insert/Name/Define and then in the Dialog box that opens, type Holidays in the
top box; and the range D1:D10 into the refers to box. Hit <OK>.


--ron

Have you tried the formula you are giving me in an actual cell in Excel? I
put it in and get a #Name? error. Adding the contents of other cells
representing holidays doesn't correct it either. I am not sure what you are
trying to say since I cannot see it. The only way I will see it is if the
formula can be copied directly from your post into my Excel program. There
has to be something I am missing. Sorry.

"Ron Rosenfeld" wrote:

> On Tue, 8 Mar 2005 22:33:03 -0800, "Monique"
> <[email protected]> wrote:
>
> >Hi Ron,
> >I am new to Excel and unfamiliar with the order and syntax of functions and
> >formulas. I am using my speadsheet to learn about Excel functions, and to
> >automate one of my tasks at work. I am not sure how to include holidays
> >since I do not have all of them off. I
> >
> >I did find a formula that will work, however, it does not include holidays.
> >I tried to put in the holiday function. The dialog box said I had too many
> >arguments.
>
> From HELP:
>
> Holidays is an optional list of one or more dates ...
>
> ==================
>
> What that means is that in some contiguous range, you place a list of the dates
> which represent holidays. For example:
>
> D1: 25 Mar 2005
> D2: 30 May 2005
> D3: 4 Jul 2005
> ..
> ..
> ..
> D7: 24 Dec 2005
> D8: 25 Dec 2005
>
>
> You then insert the range reference (D1:D8) into the formula I posted in place
> of the word "Holidays".
>
> OR, you can name this range Holidays. On the main menu bar:
> Insert/Name/Define and then in the Dialog box that opens, type Holidays in the
> top box; and the range D1:D10 into the refers to box. Hit <OK>.
>
>
> --ron
>

On Thu, 10 Mar 2005 15:59:02 -0800, "Monique"
<[email protected]> wrote:

>Have you tried the formula you are giving me in an actual cell in Excel? I
>put it in and get a #Name? error.

Your writing of exactly what happens when you try to use the formula is most
helpful in troubleshooting.

Again, from HELP for the WORKDAY function, it seems to describe your issue:

===========================
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
================================

Seems like you need to install the Analysis ToolPak, doesn't it?


--ron

I had the Analysis ToolPak add-in already.

"Ron Rosenfeld" wrote:

> On Thu, 10 Mar 2005 15:59:02 -0800, "Monique"
> <[email protected]> wrote:
>
> >Have you tried the formula you are giving me in an actual cell in Excel? I
> >put it in and get a #Name? error.
>
> Your writing of exactly what happens when you try to use the formula is most
> helpful in troubleshooting.
>
> Again, from HELP for the WORKDAY function, it seems to describe your issue:
>
> ===========================
> If this function is not available, and returns the #NAME? error, install and
> load the Analysis ToolPak add-in.
>
> How?
>
> On the Tools menu, click Add-Ins.
> In the Add-Ins available list, select the Analysis ToolPak box, and then click
> OK.
> If necessary, follow the instructions in the setup program.
> ================================
>
> Seems like you need to install the Analysis ToolPak, doesn't it?
>
>
> --ron
>

On Fri, 11 Mar 2005 15:45:02 -0800, "Monique"
<[email protected]> wrote:

>I had the Analysis ToolPak add-in already.

If you copied the exact formula I posted, and had the Analysis Tool Pak
installed, but did not either set up Holidays as a named range, or substitute a
range reference for it in my original formula, you would also get the #NAME!
error.


--ron

On Fri, 11 Mar 2005 15:45:02 -0800, "Monique"
<[email protected]> wrote:

>I had the Analysis ToolPak add-in already.

If my previous post did not help, kindly post back

1. Your exact formula.
2. The result of the formula.
3. The contents of the ranges referenced by your formula.


--ron

I put in the formula you gave me i.e. =WORKDAY(TODAY(),1,Holidays).

On a whim, I took out Holidays out of the formula. I found that I got the
right date for the first part of the formula after running the function. I
then create mock dates and named them like you suggested. I chose "Holidays"
for the range. I then put Holidays back into the formula. It seems to work.
This is what I wondered about with my initial question, i.e. are there
specific steps/order of syntax that I have to take when creating a formula.
Your formula suggestion is shorter than the one I initially used, and I can
add the holidays to it. It took a little bit to figure out but it works!
Thanks for your help.

"Ron Rosenfeld" wrote:

> On Fri, 11 Mar 2005 15:45:02 -0800, "Monique"
> <[email protected]> wrote:
>
> >I had the Analysis ToolPak add-in already.
>
> If my previous post did not help, kindly post back
>
> 1. Your exact formula.
> 2. The result of the formula.
> 3. The contents of the ranges referenced by your formula.
>
>
> --ron
>

On Fri, 11 Mar 2005 20:43:02 -0800, "Monique"
<[email protected]> wrote:

>On a whim, I took out Holidays out of the formula. I found that I got the
>right date for the first part of the formula after running the function. I
>then create mock dates and named them like you suggested. I chose "Holidays"
>for the range. I then put Holidays back into the formula. It seems to work.
> This is what I wondered about with my initial question, i.e. are there
>specific steps/order of syntax that I have to take when creating a formula.
>Your formula suggestion is shorter than the one I initially used, and I can
>add the holidays to it. It took a little bit to figure out but it works!
>Thanks for your help.

On my system, there does not seem to be any particular required order (Excel
2002).

I can enter the formula with Holidays (but no named range Holidays on my sheet)
and get the #NAME! error. If I then Name a range "Holidays" the error goes
away and I get a proper answer.

Either there's something different about your setup, or the first time you
named the range, it was not exactly the same Holidays as was in the formula
(perhaps an extra space or something). That's the only thing I can think of.

But I'm glad you've got it working!
--ron