Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
On Tue, 8 Mar 2005 09:39:13 -0800, "Monique"
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 |
#3
|
|||
|
|||
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" 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 |
#4
|
|||
|
|||
On Tue, 8 Mar 2005 17:03:01 -0800, "Monique"
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 |
#5
|
|||
|
|||
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" 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 |
#6
|
|||
|
|||
On Tue, 8 Mar 2005 22:33:03 -0800, "Monique"
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 |
#7
|
|||
|
|||
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" 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 |
#8
|
|||
|
|||
On Thu, 10 Mar 2005 15:59:02 -0800, "Monique"
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 |
#9
|
|||
|
|||
I had the Analysis ToolPak add-in already.
"Ron Rosenfeld" wrote: On Thu, 10 Mar 2005 15:59:02 -0800, "Monique" 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 |
#10
|
|||
|
|||
On Fri, 11 Mar 2005 15:45:02 -0800, "Monique"
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 |
#11
|
|||
|
|||
On Fri, 11 Mar 2005 15:45:02 -0800, "Monique"
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 |
#12
|
|||
|
|||
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" 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 |
#13
|
|||
|
|||
On Fri, 11 Mar 2005 20:43:02 -0800, "Monique"
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekday Function | Excel Worksheet Functions | |||
weekday function | New Users to Excel |