• Status: Solved
• Priority: Medium
• Security: Public
• Views: 269

# date formula using network days

Hi Experts excel 2007

Need a date formula in cell c2 to do the following if cells a2 and b2 have date's.

If cell a2 has a date and b2 is blank the in c2 add 7 days
If cell b2 has date and a2 is blank the b2.nj
0
route217
• 6
• 2
1 Solution

try this
``````=TEXT(IF(AND(CELL("Format",A2)="D1",B2=""),A2+7,IF(AND(CELL("Format",B2)="D1",A2=""),B2,"")),"dd/mmm/yyyy")
``````

Thanks
0

Commented:
What do you mean by

the b2.nj

Regards
0

Author Commented:
Hi itjockey

If a2 has a date and b2 is blank then a2 + 7 networkdays
If a2 is blank and b2 has a date then b2
Finally
If a2 has a date and b2 has a date then b2.
0

if you want to exclude weekend days in criteria 1 then use this
``````=TEXT(IF(AND(CELL("Format",A2)="D1",B2=""),A2+((7-(NETWORKDAYS(A2,A2+7)))+7),IF(AND(CELL("Format",B2)="D1",A2=""),B2,"")),"dd/mmm/yyyy")
``````
0

ok give me one moment as I dint refresh browser ...your comment I see after I posted.
``````=TEXT(IF(AND(CELL("Format",A2)="D1",B2=""),A2+((7-(NETWORKDAYS(A2,A2+7)))+7),IF(AND(CELL("Format",B2)="D1",A2=""),B2,"")),"dd/mmm/yyyy")
``````
0

``````=TEXT(IF(AND(CELL("Format",A2)="D1",B2=""),A2+((7-(NETWORKDAYS(A2,A2+7)))+7),IF(AND(CELL("Format",B2)="D1",A2=""),B2,IF(AND(CELL("Format",A2)="D1",CELL("Format",B2)="D1"),B2,""))),"DD-MMM-YYYY")
``````
Try This
0

do u want me to combine condition 2 & 3 in one statement ?as both way outcome is B2.

Thanks
0

Author Commented:
Excellence excellent. .
0

Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.