Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

date formula using network days

Posted on 2014-03-13
Medium Priority
266 Views
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
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 2

LVL 8

Expert Comment

ID: 39925910
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

LVL 53

Expert Comment

ID: 39925913
What do you mean by

the b2.nj

Regards
0

Author Comment

ID: 39925927
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

LVL 8

Expert Comment

ID: 39925928
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

LVL 8

Expert Comment

ID: 39925932
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

LVL 8

Accepted Solution

Naresh Patel earned 2000 total points
ID: 39925945
``````=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

LVL 8

Expert Comment

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

Thanks
0

Author Comment

ID: 39925958
Excellence excellent. .
0

LVL 8

Expert Comment

ID: 39925970
Thank You
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Course of the Month10 days, 21 hours left to enroll