IF-DATE NESTED FUNCTIONS :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 IF-DATE NESTED FUNCTIONS
 Author Thread

Kris138
Welcome to the Board

Joined: 20 Sep 2003
Posts: 5

Flag:

Status: Offline

IF-DATE NESTED FUNCTIONS

HELP!!! I am currently working on a project in which my boss wants me to calcuate the difference between two dates. I was able to calculate the difference of dates using the Networkingdays, also using the >=0 for everything that was positive and hid those 0 numbers. Everything negative he wants to see. My dilemma is we have another column in-between those two dates that needs to be re-calculated into if the "target date" is changed. Therefore, I can't find a formula that will automatically change and pick up that middle column.

Any help?? :o

Sat Sep 20, 2003 7:37 pm

just_jon
MrExcel MVP

Joined: 04 Sep 2002
Posts: 4007
Location: Huntsville AL
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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

 Microsoft Excel - t63279m.xls ___Running: xl2000 : OS = Windows ME
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D3 =

A
B
C
D
1
Net
2
Date1Other DtDate2Workdays
3
9/8/200212/12/20039/19/2003270
4

 Sheet1

[HtmlMaker 2.40] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
just_jon
Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Last edited by just_jon on Sat Sep 20, 2003 7:58 pm; edited 1 time in total

Sat Sep 20, 2003 7:54 pm

George J
Board Master

Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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

Sat Sep 20, 2003 7:55 pm

Kris138
Welcome to the Board

Joined: 20 Sep 2003
Posts: 5

Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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?

Sat Sep 20, 2003 8:25 pm

just_jon
MrExcel MVP

Joined: 04 Sep 2002
Posts: 4007
Location: Huntsville AL
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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?
_________________
just_jon
Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Sat Sep 20, 2003 8:39 pm

George J
Board Master

Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

Is this the type of thing you are looking for?

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

Sat Sep 20, 2003 8:43 pm

Kris138
Welcome to the Board

Joined: 20 Sep 2003
Posts: 5

Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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?

Sat Sep 20, 2003 8:56 pm

just_jon
MrExcel MVP

Joined: 04 Sep 2002
Posts: 4007
Location: Huntsville AL
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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

 Microsoft Excel - t63279m.xls ___Running: xl2000 : OS = Windows ME
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D3D4D5 =

A
B
C
D
1
Net
2
TA DateRT DateAPP DateWorkdays
3
9/12/2003 9/18/2003-5
4
9/12/20039/13/20039/18/2003-4
5
9/12/2003 9/10/2003
 Sheet1

[HtmlMaker 2.40] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Formula in D3, copied down is:

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

Belated welcome to the board!
_________________
just_jon
Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Sat Sep 20, 2003 9:06 pm

just_jon
MrExcel MVP

Joined: 04 Sep 2002
Posts: 4007
Location: Huntsville AL
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

Try the below instead --

 Microsoft Excel - t63279m.xls ___Running: xl2000 : OS = Windows ME
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D3D4D5D6 =

A
B
C
D
1
Net
2
TA DateRT DateAPP DateWorkdays
3
9/12/2003 9/18/2003-5
4
9/12/20039/13/20039/18/2003-4
5
9/12/2003 9/10/2003
6
9/12/20039/14/20039/10/2003
 Sheet1

[HtmlMaker 2.40] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

D3: =IF(AND(B6>A6,C6>A6),NETWORKDAYS(C6,B6),IF(C6>A6,NETWORKDAYS(C6,A6),""))
_________________
just_jon
Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Sat Sep 20, 2003 10:00 pm

Kris138
Welcome to the Board

Joined: 20 Sep 2003
Posts: 5

Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

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!

Sun Sep 21, 2003 8:58 pm

Aladin Akyurek
.

Joined: 15 Feb 2002
Posts: 14083
Location: The Hague
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

quote:
Originally posted by Kris138:
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!

=SUMPRODUCT(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(A1&":"&B1))),{1,2,3,4,5},0)))-1

would be equivalent to:

=NETWORKDAYS(A1,B1)

Sun Sep 21, 2003 9:16 pm

PaddyD
MrExcel MVP

Joined: 02 May 2002
Posts: 5959

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

"I don't currently have that add-in installed"

tools | addins |analysis tool pack

Sun Sep 21, 2003 9:24 pm

Kris138
Welcome to the Board

Joined: 20 Sep 2003
Posts: 5

Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

I currently don't have the capabilities of using a "networkingdays" add-in. But, I was able to use this formula

=IF(BY>A7,(C7-B7),IF(C7>A7,(C7-A7)," ")), BUT I am still trying to figure out how to calculate out the postive numbers in the networking day column. we don't want those to show.

any suggestions on that?

Sun Sep 21, 2003 9:27 pm

texasalynn
Board Master

Joined: 20 May 2002
Posts: 434
Location: Houston, TX
Flag:

Status: Offline

Re: IF-DATE NESTED FUNCTIONS

That function is from the analysis pack, which is what PaddyD was showing you how to add-in. This comes with the Excel software but isn't automatically added. If you add this you will have many useful functions.

HTH
texasalynn

Mon Sep 22, 2003 7:30 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group
 Need help posting your first question? Read how to post Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team Download VB HTML Maker to post your code on the board Check out our new index to 485 Excel Articles. Return to MrExcel Consulting All contents Copyright 1998-2004 by MrExcel.com If you believe information posted here is from your copyrighted source, notify us per the Terms of Use Excel is a registered trademark of the Microsoft Corporation.MrExcel is a registered trademark of Tickling Keys, Inc.