Solved

date formula using network days

Posted on 2014-03-13
9
259 Views
Last Modified: 2014-03-13
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
Comment
Question by:route217
  • 6
  • 2
9 Comments
 
LVL 8

Expert Comment

by:itjockey
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")

Open in new window


Thanks
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39925913
What do you mean by

the b2.nj


Regards
0
 

Author Comment

by:route217
ID: 39925927
Hi itjockey

I have yo apologies. ...made an error...can we correct formula,  please.

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

by:itjockey
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")

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:itjockey
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")

Open in new window

0
 
LVL 8

Accepted Solution

by:
itjockey earned 500 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")

Open in new window

Try This
0
 
LVL 8

Expert Comment

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

Thanks
0
 

Author Comment

by:route217
ID: 39925958
Excellence excellent. .
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39925970
Thank You
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now