Solved

date formula using network days

Posted on 2014-03-13
9
258 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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
What do you mean by

the b2.nj


Regards
0
 

Author Comment

by:route217
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 8

Expert Comment

by:itjockey
Comment Utility
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
Comment Utility
=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
Comment Utility
do u want me to combine condition 2 & 3 in one statement ?as both way outcome is B2.

Thanks
0
 

Author Comment

by:route217
Comment Utility
Excellence excellent. .
0
 
LVL 8

Expert Comment

by:itjockey
Comment Utility
Thank You
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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

10 Experts available now in Live!

Get 1:1 Help Now