Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
I start new spreadsheets on Jan 1st and July 1st, then click and drag them out row "A" to have 6 months of days to work with. Since I only want business days, I always have to manually go back and delete all of the Saturdays and Sundays off each sheet. Not the hardest work in the world but since I'm lazy I'd like to know if there is a way that I can let Excel 2000 delete them automatically. All I need is Monday thru Friday anyway. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
Hi
Use = WORKDAY(A2,1) in A3 and fill down Andy. "lsmft" wrote in message ... I start new spreadsheets on Jan 1st and July 1st, then click and drag them out row "A" to have 6 months of days to work with. Since I only want business days, I always have to manually go back and delete all of the Saturdays and Sundays off each sheet. Not the hardest work in the world but since I'm lazy I'd like to know if there is a way that I can let Excel 2000 delete them automatically. All I need is Monday thru Friday anyway. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
Thanks for the fast response Andy, but this is not working for me. I mistated my question though. I should have stated that I click on cell A-1 and drag across on Row 1 until I get to June 30. In this way each day will have it's own column. Sorry for my confusing question. Let's say: A-1 is Jan 1, 2006 B-1 is Jan 2, 2006 C-1 is Jan 3, 2006 etc. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
Thanks for the fast response Andy, but this is not working for me. I mistated my question though. I should have stated that I click on cell A-1 and drag across on Row 1 until I get to June 30. In this way each day will have it's own column. Sorry for my confusing question. Let's say: A-1 is Jan 1, 2006 B-1 is Jan 2, 2006 C-1 is Jan 3, 2006 etc. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
You can do exactly the same, but the other way around!
In B1 put =WORKDAY(A1,1) and fill it across! Andy. "lsmft" wrote in message ... Thanks for the fast response Andy, but this is not working for me. I mistated my question though. I should have stated that I click on cell A-1 and drag across on Row 1 until I get to June 30. In this way each day will have it's own column. Sorry for my confusing question. Let's say: A-1 is Jan 1, 2006 B-1 is Jan 2, 2006 C-1 is Jan 3, 2006 etc. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
Andy, In cell A1 I put 01/01/06 In cell B1 I put =WORDAY(A1,1) All I'm getting is #NAME? When I click and drag, I drag #NAME? horizontally across row 1. Am I doing something wrong here? -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
On Thu, 9 Mar 2006 15:19:33 -0600, lsmft
wrote: Andy, In cell A1 I put 01/01/06 In cell B1 I put =WORDAY(A1,1) All I'm getting is #NAME? When I click and drag, I drag #NAME? horizontally across row 1. Am I doing something wrong here? You need to install the Analysis ToolPak. See HELP for the WORKDAY function and it will tell you what to do when you see a #NAME error --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
Try WORKDAY instead of WORDAY!
Andy. "lsmft" wrote in message ... Andy, In cell A1 I put 01/01/06 In cell B1 I put =WORDAY(A1,1) All I'm getting is #NAME? When I click and drag, I drag #NAME? horizontally across row 1. Am I doing something wrong here? -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
Andy, Sorry for the misspelling. However I did have it spelled correctly in the Excel cell. I'm now going to see what I can find about analysis tool pack. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
"Andy" wrote:
Hi Use = WORKDAY(A2,1) in A3 and fill down Won't that only tell you which are the saturdays/sundays? I use. A1 = 01/01/06 A2 = if(workday(A1,2)=5,A1+3,A1+1) Which when dragged across will give you ony Monday - Friday's without the need to delete anything. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
On Fri, 10 Mar 2006 03:16:27 -0800, PaulW
wrote: "Andy" wrote: Hi Use = WORKDAY(A2,1) in A3 and fill down Won't that only tell you which are the saturdays/sundays? I use. A1 = 01/01/06 A2 = if(workday(A1,2)=5,A1+3,A1+1) Which when dragged across will give you ony Monday - Friday's without the need to delete anything. You might want to consider the difference between the functions WORKDAY and WEEKDAY. As written, the conditional portion of your equation in A2 (and as copied down), will ALWAYS evaluate to FALSE. --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Business Days Only
PaulW Wrote: "Andy" wrote: Hi Use = WORKDAY(A2,1) in A3 and fill down Won't that only tell you which are the saturdays/sundays? I use. A1 = 01/01/06 A2 = if(workday(A1,2)=5,A1+3,A1+1) Which when dragged across will give you ony Monday - Friday's without the need to delete anything. As Ron says, that won't work as it stands. If you don't have access to Analysis ToolPak you could use = if(weekday(A1)=6,A1+3,A1+1) but that only works if A1 is a weekday (which 01/01/2006 isn't) - and it doesn't exclude holidays, although with a more complex formula you could incorporate both -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=520746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many business days were in 2005? | Excel Worksheet Functions | |||
Circular Equation? wrap on 5 business days.. | Excel Discussion (Misc queries) | |||
With this business with ONLY 5,- EUR OVER 61,370 EUR in 3 months earn!! | Charts and Charting in Excel | |||
With this business with ONLY 5,- EUR OVER 61,370 EUR in 3 months earn!! | Charts and Charting in Excel | |||
How would I change a date cell to decrease it by business days? | Excel Discussion (Misc queries) |