• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF cell has formula in it already to return blank

T

traceym

New Member
Hi,

I need a cell to calculate the difference between today and another date.

The cell that has the other date in it already has a formula in it & returns #VALUE!

They are as follows:

=IF(ISBLANK(H11)," ",H11+21) - This is correct. I want this cell to add another 21 days onto a date

=IF(ISBLANK(I11)," ",I11-$T$3) - I need this to return blank if the cells return blank


$T$3 is today's date
 
Hi, traceym!


The difference can be easily calculated as:

=TODAY()-<date_cell>

=NOW()-<date_cell>


Where <date_cell> is the reference for the cell containing the date, either value or formula. User first formula for calculate difference rounded to days, and second for exact difference.


Now, checking your formulas, both of them seems to be right, so it all depends on the actual contents of H11, I11 and T3 cells. Can you post here the values displayed, the real contents (if formulas, the dependent cells too), and the format given to each one?


Otherwise consider uploading a sample file. See second green sticky post at this forums main page for guidelines.


Regards!
 
Hi, traceym!


I don't see any !#VALUE# error in your file. I tried entering a date in H11 and it works, same if I leave it blank: error if I type an space, which is logical. Where do you get that value displayed? If in second sheet, column K rows 89-90-92, I can't see what's displayed because it's protected and I can't increase column width, but they have correct date values stored.


Regards!
 
I'm sorry, please try again!

downloadlink;

https://rapidshare.com/files/63732232/Contract Report_BEN.xlsx;
 
I want cell k11 to return blank if there is not a date at all in H11.

Currently it returns #VALUE! if cell H11 is left blank
 
Hi Tracey ,


Change your formula from :


=IF(ISBLANK(I11)," ",I11-$T$3)


to


=IF(I11=" "," ",I11-$T$3) or =IF(I11=" ",I11,I11-$T$3)


Please note that a blank is the absence of any character ; blank is not the same as space , and when your formula finds H11 blank , it puts a space ( " " ) character in I11. If you test ISBLANK(I11) it will return FALSE , since a space character is present in I11 ; I11 is not blank.


Narayan
 
Hi, am rying to create an IF nested formula that attaches numeric values to the values in a cell. works for one cell but on copying it to the other cells in subsequent rows, the others return 0. The formula I am using is =IF(AN2="Major Problem",1,IF(AN2="Problem",2,IF(AN2="Minor Problem",3,IF(AN2="Not a problem",4,IF(AN2="N/A",5,"")))))

Please help!!
 
Hi Petronilla,

Welcome to the Forum..

As we told every New Member to Search before post, but you are exceptional..

As you searched and post your question in some Different Topic..


If you failed to search as per your requirement, then, at the top of the page "Add New Topic" is also available..


And about your question..

Please change the formula to below so that it can be dragable to same Row..


Code: 
=IF($AN2="Major Problem",1,IF($AN2="Problem",2,IF($AN2="Minor Problem",3,IF($AN2="Not a problem",4,IF($AN2="N/A",5,"")))))


For more detail, visit below page.

http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/


Wish you a Happy Journey with Chandoo's Forum..


Regards,

Deb


EDIT : Ninja's... Can someone move this to a New Topic..:)
 
@petronilla

Hi!

Would you please start a new topic and pasting there your question? Thank you.

Regards!


@Debraj Roy

Hi!

Neither Chandoo nor Harry Potter are here around :(

Regards!
 
You must log in or register to reply here.
Back
Top