IF-DATE NESTED FUNCTIONS

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Sep 20, 2003

• #2

Can you post -- using Colo's downloadable utility below -- a screenshot of your worksheet? What would you want done with a layout like this --

t63279m.xls
	A	B	C	D
1				Net
2	Date1	OtherDt	Date2	Workdays
3	9/8/2002	12/12/2003	9/19/2003	270
4
Sheet1

 

Upvote 0

George J

Well-known Member

Joined

Feb 15, 2002

Messages

959

• Sep 20, 2003

• #3

Sounds fairly straightforward, but can you supply some data so that we can see exactly what you would like.

 

Upvote 0

K

Kris138

New Member

Joined

Sep 20, 2003

Messages

5

• Sep 20, 2003

• #4

Jon-Yours was sort of was I was trying to do. Here is a snapshot with my formula.

TA RT App Date Variance


9/12/03 9/13/03 9/18/03 #VALUE!
9/12/03 9/18/03 -6
09/15/03 9/15/03

Employees will send me that TA Date and the APP Date. We want to make the Variance number appear only if it is negative. Ex..the -6....but, employees will send me an a RT date (Revised date), so the formula in the Variance column must reflect that also
Sorry - I tried to send a snapshot, but I am very new at this message board!!!

THANKSSS!!!!
Any suggestions?

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Sep 20, 2003

• #5

in your 1st line, w/ the 3 dates -- what is the number you would want to see were you are currently getting the VALUE error?

 

Upvote 0

George J

Well-known Member

Joined

Feb 15, 2002

Messages

959

• Sep 20, 2003

• #6

Is this the type of thing you are looking for?

=IF(B2<>"",B2-C2,A2-C2)

 

Upvote 0

K

Kris138

New Member

Joined

Sep 20, 2003

Messages

5

• Sep 20, 2003

• #7

Both of your answers have been on the right track.

The # Value is coming up because I put a nested function (defined them so that I could get over 7 functions into it), the formula was =If(or(X4-Z4)<=0,(X4-Z4),(Y4-Z4)>=0," ",IF(OR(X4-Z4)>=0," ", (Y4-Z4)<=0,(Y4-Z4))).\

Meaning, for example, the first row would count the days 9/18/03 (Approval Date) to Target Date (9/12/03). It was -6 days overdue. But, there was a revised target date (the middle number) on 9/13/03, making the Variance column to be -5 instead.

Maybe I am doing this all wrong?

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Sep 20, 2003

• #8

Going to have to leave now, but see if the below is a start:

t63279m.xls
	A	B	C	D
1				Net
2	TADate	RTDate	APPDate	Workdays
3	9/12/2003		9/18/2003	-5
4	9/12/2003	9/13/2003	9/18/2003	-4
5	9/12/2003		9/10/2003
Sheet1

Formula in D3, copied down is:

=IF(B3>A3,NETWORKDAYS(C3,B3),IF(C3>A3,NETWORKDAYS(C3,A3),""))

Belated welcome to the board!

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Sep 20, 2003

• #9

Try the below instead --

t63279m.xls
	A	B	C	D
1				Net
2	TADate	RTDate	APPDate	Workdays
3	9/12/2003		9/18/2003	-5
4	9/12/2003	9/13/2003	9/18/2003	-4
5	9/12/2003		9/10/2003
6	9/12/2003	9/14/2003	9/10/2003
Sheet1

D3: =IF(AND(B6>A6,C6>A6),NETWORKDAYS(C6,B6),IF(C6>A6,NETWORKDAYS(C6,A6),""))

 

Upvote 0

K

Kris138

New Member

Joined

Sep 20, 2003

Messages

5

• Sep 21, 2003

• #10

I was going to try it this way, but does anyone know how to do it w/o the networkingdays? I don't currently have that add-in installed.

THANKS!

 

Upvote 0