SOLVED

I need formula help!

Copper Contributor

On Sheet 1 in a specific cell, I have it blank, unless a date is entered in a cell on Sheet 3, then this particular cell on Sheet 1 has a specific value entered.  What I would like to do is if a text is entered, instead of a date, into the cell on Sheet 3, then I would like the cell on Sheet 1 to have a 0 entered.  How do I go about doing this?  So far I have this information entered:

 

In Sheet1!F75, I have this formula:

 

=IF(OR(ISBLANK(Sheet3!C4),ISBLANK(C87)),””,C87)

 

This formula keeps cell 75 on sheet 1 blank, unless I enter a date in the cell on sheet 3.  What can I add to this formula to make Sheet1!F75 have a value of 0 if the word “SKIPPED” is entered into Sheet3!C4

 

With trying different formulas, I enter the following into Sheet1!F75, but either TRUE or FALSE shows up in the cell.

 

=IF(OR(ISBLANK(Sheet3!C4),ISBLANK(C87)),””,C87)=IF(Sheet3!C4=“SKIPPED”,0,C87)

 

What am I doing wrong?  If I enter each formula separately in the cell, they work, but I need to combine them.

 

I hope that I am making sense.  I am fairly new to using excel formulas.

 

Thank you in advance!

2 Replies
best response confirmed by XxFiFTy2xX (Copper Contributor)
Solution

@XxFiFTy2xX Try this:

 

=IF(ISNUMBER(Sheet3!C4),C87,IF(Sheet3!C4="SKIPPED",0,""))

 

Note that in Excel, dates are in fact sequential numbers starting at 1 for January one, 1900. so, today's date is day number 45017. Thus, the first IF checks if a number is entered. If true then return C87. If false, check if the word SKIPPED is entered (not case-sensitive, by the way). If true then return 0, if false return an empty string.

@Riny_van_Eekelen thank you so much!  That formula did exactly what I was looking to do!

1 best response

Accepted Solutions
best response confirmed by XxFiFTy2xX (Copper Contributor)
Solution

@XxFiFTy2xX Try this:

 

=IF(ISNUMBER(Sheet3!C4),C87,IF(Sheet3!C4="SKIPPED",0,""))

 

Note that in Excel, dates are in fact sequential numbers starting at 1 for January one, 1900. so, today's date is day number 45017. Thus, the first IF checks if a number is entered. If true then return C87. If false, check if the word SKIPPED is entered (not case-sensitive, by the way). If true then return 0, if false return an empty string.

View solution in original post