?
Solved

date formula using network days

Posted on 2014-03-13
9
Medium Priority
?
265 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
[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
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:Naresh Patel
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
 
LVL 8

Expert Comment

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

Open in new window

Try This
0
 
LVL 8

Expert Comment

by:Naresh Patel
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:Naresh Patel
ID: 39925970
Thank You
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 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