Solved

date formula using network days

Posted on 2014-03-13
9
261 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 50

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
randomize data from excell cell 15 45
Limit the # times a macro code will run 14 50
first name and last initial in excel 11 28
Need help with Clear Macro 4 25
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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