IF-DATE NESTED FUNCTIONS :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

IF-DATE NESTED FUNCTIONS
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Kris138
Welcome to the Board


Joined: 20 Sep 2003
Posts: 5

Flag: Usa

Status: Offline

 Reply with quote  

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

Post Sat Sep 20, 2003 7:37 pm 
 View user's profile Send private message

just_jon
MrExcel MVP


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

Status: Offline

 Reply with quote  

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
=

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

Post Sat Sep 20, 2003 7:54 pm 
 View user's profile Send private message

George J
Board Master


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

Status: Offline

 Reply with quote  

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

Post Sat Sep 20, 2003 7:55 pm 
 View user's profile Send private message Send e-mail

Kris138
Welcome to the Board


Joined: 20 Sep 2003
Posts: 5

Flag: Usa

Status: Offline

 Reply with quote  

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?

Post Sat Sep 20, 2003 8:25 pm 
 View user's profile Send private message

just_jon
MrExcel MVP


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

Status: Offline

 Reply with quote  

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]

Post Sat Sep 20, 2003 8:39 pm 
 View user's profile Send private message

George J
Board Master


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

Status: Offline

 Reply with quote  

Re: IF-DATE NESTED FUNCTIONS

Is this the type of thing you are looking for?

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

Post Sat Sep 20, 2003 8:43 pm 
 View user's profile Send private message Send e-mail

Kris138
Welcome to the Board


Joined: 20 Sep 2003
Posts: 5

Flag: Usa

Status: Offline

 Reply with quote  

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?

Post Sat Sep 20, 2003 8:56 pm 
 View user's profile Send private message

just_jon
MrExcel MVP


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

Status: Offline

 Reply with quote  

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
=

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]

Post Sat Sep 20, 2003 9:06 pm 
 View user's profile Send private message

just_jon
MrExcel MVP


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

Status: Offline

 Reply with quote  

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
=

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]

Post Sat Sep 20, 2003 10:00 pm 
 View user's profile Send private message

Kris138
Welcome to the Board


Joined: 20 Sep 2003
Posts: 5

Flag: Usa

Status: Offline

 Reply with quote  

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!

Post Sun Sep 21, 2003 8:58 pm 
 View user's profile Send private message

Aladin Akyurek
.


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

Status: Offline

 Reply with quote  

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)

Post Sun Sep 21, 2003 9:16 pm 
 View user's profile Send private message

PaddyD
MrExcel MVP


Joined: 02 May 2002
Posts: 5959


Status: Offline

 Reply with quote  

Re: IF-DATE NESTED FUNCTIONS

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

tools | addins |analysis tool pack

Post Sun Sep 21, 2003 9:24 pm 
 View user's profile Send private message

Kris138
Welcome to the Board


Joined: 20 Sep 2003
Posts: 5

Flag: Usa

Status: Offline

 Reply with quote  

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?

Post Sun Sep 21, 2003 9:27 pm 
 View user's profile Send private message

texasalynn
Board Master


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

Status: Offline

 Reply with quote  

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

Post Mon Sep 22, 2003 7:30 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You 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 Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

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.