If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change in time where text is involved
I am working on a report where I am trying to show the
amount of time that passed between when we received a completed contract and when the installlation occured. The catch to this is I have three columns I am working with to get this (1) Date the completed contract is received (which is sometimes left blank) (2) Initial date set for Install (which is sometimes left blank)and (3)a Possible Revised Date if the Initial Date can not be meet (which is often left blank...or filled with some text explaining "no date" or "NA") So what I am looking to do is say how much time passes from the date the completed contract is received and the greater of the two dates out of the Initial Install Date and the Possible Revised Date. The formula that I have been working on has covered all the issues I have had with the eight different people filling this thing out except the text people will put in the "Possible Revised Date" column. When ever text falls into the equation I get "#Value!" which I have been told they want to avoid. So finally getting to my point how do I pull this information and have the equation show "NA" whenever there is text in the Possible Revised Date column. Currently my formula is: =IF($H31,"NA",IF($K3$M3,$M3-$H3,IF(($K3-$H3) 27000,"NA",IF($K3$M3,$K3-$H3,IF($K31,"NA"))))) H= Date Completed Contract is Received K= Initial Date for Install M= Possible Revised Date |
#2
|
|||
|
|||
Change in time where text is involved
Try using the IESRROR function. This will return whatever
you tell it to in place of error messages. I have inserted the word "FIX" which you can replace with "NA" if that is what you want it to return. =IF(ISERROR(IF($H31,"NA",IF($K3$M3,$M3-$H3,IF(($K3-$H3) 27000,"NA",IF($K3$M3,$K3-$H3,IF($K31,"NA")))))),"FIX", (IF($H31,"NA",IF($K3$M3,$M3-$H3,IF(($K3-$H3) 27000,"NA",IF($K3$M3,$K3-$H3,IF($K31,"NA"))))))) Hope it helps Michael -----Original Message----- I am working on a report where I am trying to show the amount of time that passed between when we received a completed contract and when the installlation occured. The catch to this is I have three columns I am working with to get this (1) Date the completed contract is received (which is sometimes left blank) (2) Initial date set for Install (which is sometimes left blank)and (3)a Possible Revised Date if the Initial Date can not be meet (which is often left blank...or filled with some text explaining "no date" or "NA") So what I am looking to do is say how much time passes from the date the completed contract is received and the greater of the two dates out of the Initial Install Date and the Possible Revised Date. The formula that I have been working on has covered all the issues I have had with the eight different people filling this thing out except the text people will put in the "Possible Revised Date" column. When ever text falls into the equation I get "#Value!" which I have been told they want to avoid. So finally getting to my point how do I pull this information and have the equation show "NA" whenever there is text in the Possible Revised Date column. Currently my formula is: =IF($H31,"NA",IF($K3$M3,$M3-$H3,IF(($K3-$H3) 27000,"NA",IF($K3$M3,$K3-$H3,IF($K31,"NA"))))) H= Date Completed Contract is Received K= Initial Date for Install M= Possible Revised Date . |
Thread Tools | |
Display Modes | |
|
|