| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove holidays from calculation
=IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1)
This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
Someone didn't read the help files for the NETWORKDAYS function...
"Syntax: NETWORKDAYS(start_date,end_date,holidays) ..... ..... Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates." -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
If text is not a number, the Value function will return #VALUE!
Look here for detailed description: http://www.techonthenet.com/excel/formulas/value.php I couldn't reproduce the #VALUE! scenario that you described, but maybe some of those cells are formatted as text? Try to convert (select right-click format cells date) them all to values and see what happens. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sal" wrote: =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
Someone didn't read the OP's complete message and that person is... me!
Sorry, I didn't see your whole message (mainly because I didn't scroll down to the bottom of it). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Someone didn't read the help files for the NETWORKDAYS function... "Syntax: NETWORKDAYS(start_date,end_date,holidays) ..... ..... Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates." -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
=NETWORKDAYS(K13,L14,Holidays!A1:A29)-1
How about this... =IF(OR(K13="",L14=""),"",NETWORKDAYS(K13,L14,Holid ays!A1:A29)-1) -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
Hello. Thank you for the tip. I tried out the conversion suggestions and I
got the same result. I think the problem might be due to the fact that another formula resides in Column K that will only pull data into the cell if certain conditions are met. Since I am only having problems with those cells that are blank I am thinking this is the cause of the problem. This also might be why you cannot reproduce the problem That being said, do you know how else I might be able to resolve the problem I am having? "ryguy7272" wrote: If text is not a number, the Value function will return #VALUE! Look here for detailed description: http://www.techonthenet.com/excel/formulas/value.php I couldn't reproduce the #VALUE! scenario that you described, but maybe some of those cells are formatted as text? Try to convert (select right-click format cells date) them all to values and see what happens. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sal" wrote: =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
Hi Rick. Thank you for the input. I am looking to reference a list of
holidays from a range that contains the dates in a worksheet called Holidays. I am not sure I completely understand. Are you recommending to include NETWORKDAYS(start_date,end_date,holidays) somewhere in this formula? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 "Rick Rothstein" wrote: Someone didn't read the help files for the NETWORKDAYS function... "Syntax: NETWORKDAYS(start_date,end_date,holidays) ..... ..... Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates." -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
I'm still not sure what the problem is here. Click the little question mark
symbol in the Main Excel view. Type 'NETWORKDAYS' and yo will see a good example of this this function works. Copy/paste the example into a sheet (fills cells A1:C7), and enter this function into cell E3; =NETWORKDAYS(B3,B4,B5:B7) What result do you get? 108? That is correct. Look at the logic. I renamed a sheet to Holidays and entered =TODAY() in Cell A1. in A2 I put =A1+1 then filled down until A29. now, back to the first sheet, enter this in cell E3: =NETWORKDAYS(B3,B4,Holidays!A1:A29) What result do you get? You should get 88. try it. Try to adopt that for your specific example. Post back with any additional, specific, questions. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sal" wrote: Hi Rick. Thank you for the input. I am looking to reference a list of holidays from a range that contains the dates in a worksheet called Holidays. I am not sure I completely understand. Are you recommending to include NETWORKDAYS(start_date,end_date,holidays) somewhere in this formula? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 "Rick Rothstein" wrote: Someone didn't read the help files for the NETWORKDAYS function... "Syntax: NETWORKDAYS(start_date,end_date,holidays) ..... ..... Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates." -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
Wow this is great. I think the problem I am having might have been specific
to the worksheet. Thank you a lot for your help with this. I appreciate it very much. "ryguy7272" wrote: I'm still not sure what the problem is here. Click the little question mark symbol in the Main Excel view. Type 'NETWORKDAYS' and yo will see a good example of this this function works. Copy/paste the example into a sheet (fills cells A1:C7), and enter this function into cell E3; =NETWORKDAYS(B3,B4,B5:B7) What result do you get? 108? That is correct. Look at the logic. I renamed a sheet to Holidays and entered =TODAY() in Cell A1. in A2 I put =A1+1 then filled down until A29. now, back to the first sheet, enter this in cell E3: =NETWORKDAYS(B3,B4,Holidays!A1:A29) What result do you get? You should get 88. try it. Try to adopt that for your specific example. Post back with any additional, specific, questions. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sal" wrote: Hi Rick. Thank you for the input. I am looking to reference a list of holidays from a range that contains the dates in a worksheet called Holidays. I am not sure I completely understand. Are you recommending to include NETWORKDAYS(start_date,end_date,holidays) somewhere in this formula? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 "Rick Rothstein" wrote: Someone didn't read the help files for the NETWORKDAYS function... "Syntax: NETWORKDAYS(start_date,end_date,holidays) ..... ..... Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates." -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove holidays from calculation
Thank you Rick. This works well. I think you figured it out. I am thankful
for your help. Thank you! "Rick Rothstein" wrote: =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 How about this... =IF(OR(K13="",L14=""),"",NETWORKDAYS(K13,L14,Holid ays!A1:A29)-1) -- Rick (MVP - Excel) "Sal" wrote in message ... =IF(COUNT(K13:L13)<2,"",NETWORKDAYS(K13,L13)-1) This formula already excludes weekends from the calculation. Do you know how I could improve this formula so that it does not include holidays in the calculation, I can specify the holidays, and I will not get an error message if Column K is blank? Or Do you know how I can improve this formula so that if Column K is blank I will not get a #VALUE! message where the calculation should be? =NETWORKDAYS(K13,L14,Holidays!A1:A29)-1 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consecutive date calculation involving holidays | Excel Worksheet Functions | |||
| how do I remove #DIV/0! in a pivot table calculation field ? | Excel Discussion (Misc queries) | |||
| Date Calculation Formula- Calendar days minus Holidays | Excel Worksheet Functions | |||
| Holidays | Excel Worksheet Functions | |||
| Holidays | Excel Worksheet Functions | |||
Linear Mode
