| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Dates with Empty Cells
I am using this formula to figure out how many days are between two dates
without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
Add this to the beginning of the formula:
=IF(COUNT(J15:K15)<2,0, And add a closing ")" to the very end of the formula -- Biff Microsoft Excel MVP "Workbook" wrote in message ... I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
One way:
=IF(COUNT(J15:K15)<2,"",<your formula here) In article , Workbook wrote: I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD( K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
=IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")"
was not working so I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1) instead and had some success because the cell went blank, but I realized I need for the cell to change to "0", otherwise other formulas get fowled up. I also noticed next to the cells that didn't go blank an exclamation point that when I clicked it gave me this message"The formula in this cell refers to a range that has additional numbers adjacent to it." Do you have any thoughts? Thank you for your feedback. "T. Valko" wrote: Add this to the beginning of the formula: =IF(COUNT(J15:K15)<2,0, And add a closing ")" to the very end of the formula -- Biff Microsoft Excel MVP "Workbook" wrote in message ... I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
Thank you for your input.
=IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6)) was not working and I would get a message that said "#VALUE! in the cell, so I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1) instead and had some success because the cell went blank. However, I realized I need for the cell to change to "0", otherwise other formulas get fowled up. I also noticed next to the cells that didn't go blank an exclamation point that when I held my pointer over it I got the message"The formula in this cell refers to a range that has additional numbers adjacent to it." What do you think? "JE McGimpsey" wrote: One way: =IF(COUNT(J15:K15)<2,"",<your formula here) In article , Workbook wrote: I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD( K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
=IF(COUNT(J15:K15)<2,
=IF(COUNT(J15:K15)<2, There is essentially no difference between the 2 of those expressions. I see at the end of the formula you have: ......)<6).")" It should be: ......)<6)) About that message, I would just ignore it. I have all those error checking messages turned off. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... =IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")" was not working so I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1) instead and had some success because the cell went blank, but I realized I need for the cell to change to "0", otherwise other formulas get fowled up. I also noticed next to the cells that didn't go blank an exclamation point that when I clicked it gave me this message"The formula in this cell refers to a range that has additional numbers adjacent to it." Do you have any thoughts? Thank you for your feedback. "T. Valko" wrote: Add this to the beginning of the formula: =IF(COUNT(J15:K15)<2,0, And add a closing ")" to the very end of the formula -- Biff Microsoft Excel MVP "Workbook" wrote in message ... I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
Thanks Man. I must be typing something incorrectly. However, I am having
success with this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1), so I'll probably stick with it. Thank you again for your input! "T. Valko" wrote: =IF(COUNT(J15:K15)<2, =IF(COUNT(J15:K15)<2, There is essentially no difference between the 2 of those expressions. I see at the end of the formula you have: ......)<6).")" It should be: ......)<6)) About that message, I would just ignore it. I have all those error checking messages turned off. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... =IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")" was not working so I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1) instead and had some success because the cell went blank, but I realized I need for the cell to change to "0", otherwise other formulas get fowled up. I also noticed next to the cells that didn't go blank an exclamation point that when I clicked it gave me this message"The formula in this cell refers to a range that has additional numbers adjacent to it." Do you have any thoughts? Thank you for your feedback. "T. Valko" wrote: Add this to the beginning of the formula: =IF(COUNT(J15:K15)<2,0, And add a closing ")" to the very end of the formula -- Biff Microsoft Excel MVP "Workbook" wrote in message ... I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting Dates with Empty Cells
You're welcome!
-- Biff Microsoft Excel MVP "Workbook" wrote in message ... Thanks Man. I must be typing something incorrectly. However, I am having success with this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1), so I'll probably stick with it. Thank you again for your input! "T. Valko" wrote: =IF(COUNT(J15:K15)<2, =IF(COUNT(J15:K15)<2, There is essentially no difference between the 2 of those expressions. I see at the end of the formula you have: ......)<6).")" It should be: ......)<6)) About that message, I would just ignore it. I have all those error checking messages turned off. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... =IF(COUNT(J15:K15)<2,0,NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6).")" was not working so I tried =IF(COUNT(J15:K15)<2,"",NETWORKDAYS(J15,K15)-1) instead and had some success because the cell went blank, but I realized I need for the cell to change to "0", otherwise other formulas get fowled up. I also noticed next to the cells that didn't go blank an exclamation point that when I clicked it gave me this message"The formula in this cell refers to a range that has additional numbers adjacent to it." Do you have any thoughts? Thank you for your feedback. "T. Valko" wrote: Add this to the beginning of the formula: =IF(COUNT(J15:K15)<2,0, And add a closing ")" to the very end of the formula -- Biff Microsoft Excel MVP "Workbook" wrote in message ... I am using this formula to figure out how many days are between two dates without including the weekends. =NETWORKDAYS(J15,K15)-(WEEKDAY(J15,2)<6)+(1-MOD(3,1))*(WEEKDAY(J15,2)<6)+(MOD(K15,1)-1)*(WEEKDAY(K15,2)<6). I have placed this formula in cell N15. It works very well, however in cell N15 the columns I am subtracting (cell J15 and column K15) are missing dates. Could you tell me what I could include in this formula so that it does not work when a cell in column J and/or column K is missing a date? What I would like to happen instead is for cell N15 (which contains the formula) to be blank or contain a zero inside of it, when dates are missing from J15 and/or K15. Is this possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting days comparing 2 dates excluding empty cells | Excel Worksheet Functions | |||
| Subtracting Dates | Excel Discussion (Misc queries) | |||
| Subtracting Dates | Excel Discussion (Misc queries) | |||
| Subtracting dates? | Excel Worksheet Functions | |||
| Subtracting dates: 8/31/05-8/1/05? | Excel Discussion (Misc queries) | |||
Linear Mode
