Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Changing the value resulting from a formula only if it equals a specific value

0

The following formula brings back a time from another worksheet.  If the time returned is 02:59 I would like to display the text "ongoing" in the cell.  Otherwise display what ever time is returned.

Here is the formula.

=IFERROR(INDEX(Sheet1!$A$1:$S$500,MATCH(AH57,Sheet1!$I$1:$I$500,FALSE),2),"")

excel
MS Office Version: office 365 for Windows 11
posted by: n180093 on Sat Apr 21, 2018 at 8:31 am
Answer
Discuss

Answers

1

Please try this formula.

=IF(TEXT(IFERROR(INDEX(Sheet1!$A$1:$S$500,MATCH(AH57,Sheet1!$I$1:$I$500,FALSE),2),""),"hh:mm")="02:59","ongoing",IFERROR(INDEX(Sheet1!$A$1:$S$500,MATCH(AH57,Sheet1!$I$1:$I$500,FALSE),2),""))

In order to explain it, here is the short version of it.

=IF(TEXT(D4,"hh:mm")="02:59,"ongoing",D4)

In the long version "D4" is replaced with your original formula, twice. The difference is that the formula looks for a number that translates into a displayed value of 02:59. That allows for a lot of leeway around the more precise value of  0.124305555555556.

However, =TEXT(D4,"hh:mm") returns 14:09 if D4 contains a string (text value) of "02:59". I feel that your referenced cell, more likely, actually does contain a text value, at least when it displays "02:59" and perhaps a real time at other times. Fake time, one would say now a-days lol: In the case of a fake time the short formula would have to look like this.

IF(D4="02:59","ongoing",D4)

Or you might cover both possibilities with this formula.

IF(OR(TEXT(D4,"hh:mm")="02:59",D4="02:59"),"ongoing",D4)

In both cases replace all occurrences of D4 with your original formula (which I couldn't test). And, in any event, watch the formatting of the cell where you have the formula. It must be designed to take the value from the referenced cell. Excel will copy the format from that source for good emasure if left to its own devices. However, I suppose you have interfered so much already that you will need to take full control.

posted by: Variatus (rep: 4889) on Sat Apr 21, 2018 at 9:47 pm
Discuss

Discussion

Your formula worked for me.  Thanks so much for the help.
n180093 Apr 22, '18 at 12:02 pm
I'm glad you got it working. Please accept the answer as a beacon for othrers who may have a similar question (and as a reward for me for having spent my time). Thank you.
Variatus (rep: 4889) Apr 22, '18 at 8:18 pm
I thought I had this one haha - I guess I was too slow in replying and your damn answers are just so thorough! Upvote from me!
don (rep: 1989) Apr 23, '18 at 10:20 am
Thanbk you Don. I thought we ought to be able to make the difference between our time zones work to the forum's advantage.
Variatus (rep: 4889) Apr 23, '18 at 8:41 pm
Add to Discussion
0

You need to check if it is equal to 0.124305555555556 for 2:59 AM. Time is stored as a decimal.

Try the below formula:

=IFERROR(IF(INDEX(Sheet1!$A$1:$S$500,MATCH(AH57,Sheet1!$I$1:$I$500,FALSE),2)=0.124305555555556,"ongoing",INDEX(Sheet1!$A$1:$S$500,MATCH(AH57,Sheet1!$I$1:$I$500,FALSE),2)),"")
posted by: don (rep: 1989) on Sat Apr 21, 2018 at 12:37 pm
Discuss

Discussion

Hi Don, thanks for the quick reply.  When I try your formula, I get the value 43279.12 returned.  Acutally if I replace the 0.124305555555556 with any numerical value I get the same outcome.  I also tried using time(2,50,0) and result is the same.
n180093 Apr 21, '18 at 1:09 pm
You have to format the cell. The returned value includes the date (43279) and the time (0.12 with the further decimals truncated - more likely rounded by your cell format). If you format the cell as Custom Time hh:mm it will display 02:52. The difference results from the truncated decimals. If your actual cell value is not rounded 02.59 should show.
However, I'm afraid Don's suggestion can't ever work because your referenced cell will never have the exact time value for 02:59 because even 0.124305555555556 is rounded. It may show 02:59 but that is a truncated value and the underlying real decimal value will be different. That's why I will submit another answer below, in a minute.
Variatus (rep: 4889) Apr 21, '18 at 9:23 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login