Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
In help lookup the NETWORKDAYS function.
NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the information it seems as though I need to have a definite start date and end date. Is there a way around this to keep a continuous worksheet? If I have to put dates how far out do the dates go? Say a year or longer? And to enter the "holidays" do I just create a new column and list one date on each row? "Kevin B" wrote: In help lookup the NETWORKDAYS function. NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
I think you want =Workday(), too.
I'd create a new sheet--maybe even hide that sheet when I was done. Put all the holidays in column A. Just keep adding to the bottom of the list--don't leave any gaps. Then use a dynamic range name that points at that ever growing list of holidays. See Debra Dalgeish's site for some nice tips: http://contextures.com/xlNames01.html#Dynamic Try ignoring the holidays with a formula like: =WORKDAY(A1,4) Then you can your named range of holidays after you type it in. Remember that =workday() requires the analysis toolpak addin to be checked (tools|addins). DRondeau wrote: Thank you for responding so quickly! :) I am not very proficient with Excel so excuse me if I seem ignorant...when I go into Help and pull up the information it seems as though I need to have a definite start date and end date. Is there a way around this to keep a continuous worksheet? If I have to put dates how far out do the dates go? Say a year or longer? And to enter the "holidays" do I just create a new column and list one date on each row? "Kevin B" wrote: In help lookup the NETWORKDAYS function. NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
Go to a blank worksheet and in a column of your choosing enter in the
holidays, 1 per row. The dates below are the holidays of the company Im working for. 9/4/2006 10/09/2006 11/23/2006 11/24/2006 12/25/2006 12/26/2006 You can extend the number of holidays out as far as you wish and when all holidays have been entered, click and drag from the first holiday date to the last holiday date to select them all. Then click INSERT on the menu, select NAME and then DEFINE and enter the name Holidays and click OK. I did not read your prior post close enough and the NETWORKDAYS is for calculating the number of work days between 2 dates, what you want is the WORKDAY function. For example, if A1 has a date and I want to calculate 4 working days from that date the and display the result in column B, the formula would be: WORKDAY(A1,4, Holidays) Or you could do the following: Col A Col B Col C 9/5/2006 6 WORKDAY(A1,B1, Holidays) You could also use and IF function so that if there wasnt a date in column A the cell would be blank, that way when the formula is copied down the column you wont have any results displayed for rows that dont have a date: =IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays)) Hope this hasnt made things worse, but if it has Ill check back later to see what the next post has to say. -- Kevin Backmann "DRondeau" wrote: Thank you for responding so quickly! :) I am not very proficient with Excel so excuse me if I seem ignorant...when I go into Help and pull up the information it seems as though I need to have a definite start date and end date. Is there a way around this to keep a continuous worksheet? If I have to put dates how far out do the dates go? Say a year or longer? And to enter the "holidays" do I just create a new column and list one date on each row? "Kevin B" wrote: In help lookup the NETWORKDAYS function. NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
Hi Kevin,
I know I must seem really clueless but I just can't seem to make this work. If I have the worksheet set up with the date being entered in row G1 (hopefully don't need to specify row# because I would like to keep it as a rolling worksheet) and to utilize the "IF" formula could you show me the formula. I just can't seem to figure out what I'm missing. How do I set the formula for the entire column or is this not possible? I do have a row headings which I do not want to change. I have created another spreadsheet for my "Holidays" and would like to have this expand as I need to add other holidays. Is there a way to set up the formula for this? I am just not able to figure this out without getting more specific to my own scenario. I hope you don't mind. Thanks so much for all of your help so far...I truly appreciate it! "Kevin B" wrote: Go to a blank worksheet and in a column of your choosing enter in the holidays, 1 per row. The dates below are the holidays of the company Im working for. 9/4/2006 10/09/2006 11/23/2006 11/24/2006 12/25/2006 12/26/2006 You can extend the number of holidays out as far as you wish and when all holidays have been entered, click and drag from the first holiday date to the last holiday date to select them all. Then click INSERT on the menu, select NAME and then DEFINE and enter the name Holidays and click OK. I did not read your prior post close enough and the NETWORKDAYS is for calculating the number of work days between 2 dates, what you want is the WORKDAY function. For example, if A1 has a date and I want to calculate 4 working days from that date the and display the result in column B, the formula would be: WORKDAY(A1,4, Holidays) Or you could do the following: Col A Col B Col C 9/5/2006 6 WORKDAY(A1,B1, Holidays) You could also use and IF function so that if there wasnt a date in column A the cell would be blank, that way when the formula is copied down the column you wont have any results displayed for rows that dont have a date: =IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays)) Hope this hasnt made things worse, but if it has Ill check back later to see what the next post has to say. -- Kevin Backmann "DRondeau" wrote: Thank you for responding so quickly! :) I am not very proficient with Excel so excuse me if I seem ignorant...when I go into Help and pull up the information it seems as though I need to have a definite start date and end date. Is there a way around this to keep a continuous worksheet? If I have to put dates how far out do the dates go? Say a year or longer? And to enter the "holidays" do I just create a new column and list one date on each row? "Kevin B" wrote: In help lookup the NETWORKDAYS function. NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
Question: Are you entering in a single date into cell G1 and then
calculating date x number of of working days, or is column G going to have dates down x number of rows? I'm not sure of how you want to use this formula. If the dates are posted on Sheet1, with the holidays in Sheet2, does Sheet1 look like the following? (G1 is the starting date, H1 is the number of working days and I1 is the result of the WORKDAY formula. G H I 10/24/2006 5 10/24/2006 And does Sheet2 resemble the following: A 1 09/04/2006 2 10/11/2006 3 11/23/2006 4 11/24/2006 5 12/25/2006 6 12/26/2006 The formula I1 would be =WORKDAY(G1,H1,Sheet2!A1:A6) If you're going to have multiple dates you would enter the formula in I1 with an absolute reference to the holidays in Sheet 2: The formula I1 would be =WORKDAY(G1,H1,Sheet2!$A$1:$A$6) and then you could copy the formula down column I. The worksheet would look like this: Column A Colum B Column C 1 10/17/2006 5 10/24/2006 2 11/07/2006 10 11/21/2006 3 12/23/2006 8 01/05/2007 The dollar signs in the reference to the holidays prevent Excel from adjusting the formula by 1 row each time its copied down column I -- Kevin Backmann "DRondeau" wrote: Hi Kevin, I know I must seem really clueless but I just can't seem to make this work. If I have the worksheet set up with the date being entered in row G1 (hopefully don't need to specify row# because I would like to keep it as a rolling worksheet) and to utilize the "IF" formula could you show me the formula. I just can't seem to figure out what I'm missing. How do I set the formula for the entire column or is this not possible? I do have a row headings which I do not want to change. I have created another spreadsheet for my "Holidays" and would like to have this expand as I need to add other holidays. Is there a way to set up the formula for this? I am just not able to figure this out without getting more specific to my own scenario. I hope you don't mind. Thanks so much for all of your help so far...I truly appreciate it! "Kevin B" wrote: Go to a blank worksheet and in a column of your choosing enter in the holidays, 1 per row. The dates below are the holidays of the company Im working for. 9/4/2006 10/09/2006 11/23/2006 11/24/2006 12/25/2006 12/26/2006 You can extend the number of holidays out as far as you wish and when all holidays have been entered, click and drag from the first holiday date to the last holiday date to select them all. Then click INSERT on the menu, select NAME and then DEFINE and enter the name Holidays and click OK. I did not read your prior post close enough and the NETWORKDAYS is for calculating the number of work days between 2 dates, what you want is the WORKDAY function. For example, if A1 has a date and I want to calculate 4 working days from that date the and display the result in column B, the formula would be: WORKDAY(A1,4, Holidays) Or you could do the following: Col A Col B Col C 9/5/2006 6 WORKDAY(A1,B1, Holidays) You could also use and IF function so that if there wasnt a date in column A the cell would be blank, that way when the formula is copied down the column you wont have any results displayed for rows that dont have a date: =IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays)) Hope this hasnt made things worse, but if it has Ill check back later to see what the next post has to say. -- Kevin Backmann "DRondeau" wrote: Thank you for responding so quickly! :) I am not very proficient with Excel so excuse me if I seem ignorant...when I go into Help and pull up the information it seems as though I need to have a definite start date and end date. Is there a way around this to keep a continuous worksheet? If I have to put dates how far out do the dates go? Say a year or longer? And to enter the "holidays" do I just create a new column and list one date on each row? "Kevin B" wrote: In help lookup the NETWORKDAYS function. NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formulas
Just to clarify...Row 1 on my sheet is a column heading - does this matter?
The date in Column G will be different for most entries on spreadsheet. It will be dependent upon when the information is added. Each row will be a new date entered. The # of working days is in Column H and Column I is the result. I seem to have made the formula work on my first row - which is very exciting! Now I need to copy this down the entire column I. For the Holidays - I have them set up on sheet 2 in column A starting at row 1. I entered the formula for Column I as =WORKDAY (G2,H2,SHEET2!$A$1:$A$4) I only have 4 holidays listed so far. If I happen to add more dates will I need to change the formula in Column I to reflect this? How would I do it if I need to? I also wanted to find out if there is a way to create the formula so it won't show a date in Column I without having the information yet in Column G? When you say format the cell as DATE I need to use Insert - Function - Date or is there an easier way to format the entire Column as such? With the Insert function it seems I need to do this for each individual row (cell). Hopefully there is an easier way. I hope this answers the questions you had and hopefully didn't create more confusion...I have a tendency to do that! :) Thanks, Dione "Kevin B" wrote: Question: Are you entering in a single date into cell G1 and then calculating date x number of of working days, or is column G going to have dates down x number of rows? I'm not sure of how you want to use this formula. If the dates are posted on Sheet1, with the holidays in Sheet2, does Sheet1 look like the following? (G1 is the starting date, H1 is the number of working days and I1 is the result of the WORKDAY formula. G H I 10/24/2006 5 10/24/2006 And does Sheet2 resemble the following: A 1 09/04/2006 2 10/11/2006 3 11/23/2006 4 11/24/2006 5 12/25/2006 6 12/26/2006 The formula I1 would be =WORKDAY(G1,H1,Sheet2!A1:A6) If you're going to have multiple dates you would enter the formula in I1 with an absolute reference to the holidays in Sheet 2: The formula I1 would be =WORKDAY(G1,H1,Sheet2!$A$1:$A$6) and then you could copy the formula down column I. The worksheet would look like this: Column A Colum B Column C 1 10/17/2006 5 10/24/2006 2 11/07/2006 10 11/21/2006 3 12/23/2006 8 01/05/2007 The dollar signs in the reference to the holidays prevent Excel from adjusting the formula by 1 row each time its copied down column I -- Kevin Backmann "DRondeau" wrote: Hi Kevin, I know I must seem really clueless but I just can't seem to make this work. If I have the worksheet set up with the date being entered in row G1 (hopefully don't need to specify row# because I would like to keep it as a rolling worksheet) and to utilize the "IF" formula could you show me the formula. I just can't seem to figure out what I'm missing. How do I set the formula for the entire column or is this not possible? I do have a row headings which I do not want to change. I have created another spreadsheet for my "Holidays" and would like to have this expand as I need to add other holidays. Is there a way to set up the formula for this? I am just not able to figure this out without getting more specific to my own scenario. I hope you don't mind. Thanks so much for all of your help so far...I truly appreciate it! "Kevin B" wrote: Go to a blank worksheet and in a column of your choosing enter in the holidays, 1 per row. The dates below are the holidays of the company Im working for. 9/4/2006 10/09/2006 11/23/2006 11/24/2006 12/25/2006 12/26/2006 You can extend the number of holidays out as far as you wish and when all holidays have been entered, click and drag from the first holiday date to the last holiday date to select them all. Then click INSERT on the menu, select NAME and then DEFINE and enter the name Holidays and click OK. I did not read your prior post close enough and the NETWORKDAYS is for calculating the number of work days between 2 dates, what you want is the WORKDAY function. For example, if A1 has a date and I want to calculate 4 working days from that date the and display the result in column B, the formula would be: WORKDAY(A1,4, Holidays) Or you could do the following: Col A Col B Col C 9/5/2006 6 WORKDAY(A1,B1, Holidays) You could also use and IF function so that if there wasnt a date in column A the cell would be blank, that way when the formula is copied down the column you wont have any results displayed for rows that dont have a date: =IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays)) Hope this hasnt made things worse, but if it has Ill check back later to see what the next post has to say. -- Kevin Backmann "DRondeau" wrote: Thank you for responding so quickly! :) I am not very proficient with Excel so excuse me if I seem ignorant...when I go into Help and pull up the information it seems as though I need to have a definite start date and end date. Is there a way around this to keep a continuous worksheet? If I have to put dates how far out do the dates go? Say a year or longer? And to enter the "holidays" do I just create a new column and list one date on each row? "Kevin B" wrote: In help lookup the NETWORKDAYS function. NETWORKDAY(Date1, Date2, [Optional Holidays]) You can create a named range of all your company holidays and then use the range name as the argument value for holidays. If you get a #NAME error you need to go to TOOLS/ADD INS and check off the ANALYSIS TOOLPACK -- Kevin Backmann "DRondeau" wrote: I am trying to find a formula or function that will enable me to add 4 work days (Mon-Fri) to a date entered in another column. Dates will change. Also need to figure out how to format the range for Holidays in order for those to not be counted as well. I am not quite sure how to format the WORKDAY function if this is the appropriate choice. Any help would be most appreciated. I've been working on this for the past week and have had no luck. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Date formulas needed | Excel Discussion (Misc queries) | |||
Min/Max formulas using cells with date format | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |