# If cell contains specific text, then return a value



## ricapan (Wednesday at 9:17 AM)

Hello all,

I need some help.



I have an small excel with 5 rows and I need a formula that inserts a fix date.



The rows are:

Location-task-status-pending-completed



On the status row I have a drop-down menu with 3 options(new,pending,done)



What I exactly need is to convert this formula into a fix date:



*=IF(cell="text", value_to_return, "")



So if in “status row” I choose “done option”, in the “completed row” it shows the date this option was chosen. *



Where “value to return” is a fix date for the option chose on status and does not change unless you change on the drop-down menu.

I am attaching a screen shoot.



I thanks you in advace for you help.



Best.


----------



## aaewalsh (Wednesday at 9:24 AM)

Without using a macro you could use. add this code to the E2 and fill down

```
=IF(c2="done", NOW(),"")
```


----------



## ricapan (Wednesday at 9:32 AM)

aaewalsh said:


> Without using a macro you could use. add this code to the E2 and fill down
> 
> ```
> =IF(c2="done", NOW(),"")
> ```


Thanks for your help, but I get this and i dont know if i am missing something,  see image


----------



## Sufiyan97 (Wednesday at 9:33 AM)

comma is missing after NOW()


----------



## Fluff (Wednesday at 9:35 AM)

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the *method* of doing so is covered by #13 of the Forum Rules.

*Be sure to follow & read the link at the end of the rule too!*

Cross posted at: If cell contains specific text, then return a value
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.


----------



## ricapan (Yesterday at 3:45 AM)

Still not working, any ideas please


----------



## aaewalsh (Yesterday at 3:52 AM)

Book1ABCDEFG1DateLocationTaskStatusPendingCompleted2done12/01/2023 09:52:003new 4pending Sheet1Cell FormulasRangeFormulaG2:G4G2=IF(E2="done", NOW(),"")


----------



## aaewalsh (Yesterday at 3:53 AM)

there is an issue with this code i just noticed that every time you refresh or update any cell the time will change


----------



## ricapan (Yesterday at 4:00 AM)

I must be doing something wrong cause it does not work on mune, also my parethesis comme up always in red and are different to yours.


----------



## aaewalsh (Yesterday at 4:01 AM)

What is your file extension


----------



## ricapan (Wednesday at 9:17 AM)

Hello all,

I need some help.



I have an small excel with 5 rows and I need a formula that inserts a fix date.



The rows are:

Location-task-status-pending-completed



On the status row I have a drop-down menu with 3 options(new,pending,done)



What I exactly need is to convert this formula into a fix date:



*=IF(cell="text", value_to_return, "")



So if in “status row” I choose “done option”, in the “completed row” it shows the date this option was chosen. *



Where “value to return” is a fix date for the option chose on status and does not change unless you change on the drop-down menu.

I am attaching a screen shoot.



I thanks you in advace for you help.



Best.


----------



## ricapan (Yesterday at 4:04 AM)

new sheet


----------



## ricapan (Yesterday at 4:04 AM)

aaewalsh said:


> What is your file extension


how do i look this?


----------



## ricapan (Yesterday at 4:09 AM)




----------



## shinigamilight (Yesterday at 5:45 AM)

```
Function CURRENTTIME(rng As Range)
           
            Select Case rng
           
            Case "done"
            CURRENTTIME = Now
            Case "Done"
            CURRENTTIME = Now
           
            End Select
           
           

End Function
```

Try this code this is a custom function that might do the job.

press alt + F11 on your keyboard and click on insert and then click on module and paste this code. Then "CURRENTIME" will show up in your functions when you type "=".


----------



## ricapan (Yesterday at 6:29 AM)

shinigamilight said:


> ```
> Function CURRENTTIME(rng As Range)
> 
> Select Case rng
> ...


wow, it works, thanks a million.

2 more questions if you dont mind.
- please, can we add the pending value to be recognize?
- will the date change to very time I open the excell? Cause I need it to be static to the time "Done" was entered.
I am doing this for trace keeping so the purpose is that we keep track of the tasks at the moment that are entered(pending,completed) so if in 3 days time we open, to know when was completed  or the (pendings when was last time someone act on it). 

sorry if its too much questions

Thanks in advance for your assitance


----------



## shinigamilight (Yesterday at 6:35 AM)

ricapan said:


> wow, it works, thanks a million.
> 
> 2 more questions if you dont mind.
> - please, can we add the pending value to be recognize?
> ...


Dates & time are completely static, they will not change.
What do you mean by "Pending value to be recognized"? What do you want to appear when Pending is in the cell, I'll add that condition too.
And make sure to save your file as .xlsm instead of .xlsx, otherwise the custom formula will disappear the next time you'll open the file.


----------



## ricapan (Yesterday at 6:51 AM)

shinigamilight said:


> Dates & time are completely static, they will not change.
> What do you mean by "Pending value to be recognized"? What do you want to appear when Pending is in the cell, I'll add that condition too.
> And make sure to save your file as .xlsm instead of .xlsx, otherwise the custom formula will disappear the next time you'll open the file.


Thanks for the time answer.
What do you mean by "Pending value to be recognized"? What do you want to appear when Pending is in the cell, I'll add that condition too. 
I mean that also recognize when "pending" option was chosen, same as recognize that "done" was chosen.


----------



## shinigamilight (Yesterday at 7:00 AM)

```
Function CURRENTTIME(rng As Range)
           
            Select Case rng
           
            Case "done"
            CURRENTTIME = Now
            Case "Done"
            CURRENTTIME = Now
            Case "Pending"
            CURRENTTIME = Now
            Case "pending"
            CURRENTTIME = Now
            Case Else
            CURRENTTIME = ""
           
            End Select
           
           

End Function
```

Even added a line that if there's anything else other than pending or done in the cell, it'll return blank.


----------



## ricapan (Today at 4:27 AM)

@shinigamilight, thanks a million, it works perfectly.


----------



## ricapan (Today at 4:28 AM)

I dont know how to close the thread


----------



## ricapan (Wednesday at 9:17 AM)

Hello all,

I need some help.



I have an small excel with 5 rows and I need a formula that inserts a fix date.



The rows are:

Location-task-status-pending-completed



On the status row I have a drop-down menu with 3 options(new,pending,done)



What I exactly need is to convert this formula into a fix date:



*=IF(cell="text", value_to_return, "")



So if in “status row” I choose “done option”, in the “completed row” it shows the date this option was chosen. *



Where “value to return” is a fix date for the option chose on status and does not change unless you change on the drop-down menu.

I am attaching a screen shoot.



I thanks you in advace for you help.



Best.


----------



## Fluff (Today at 7:55 AM)

We don't close threads. All you need to do is respond saying the solution worked & mark the relevant post as the solution. 
Both of which you have already done, so all is good


----------



## ricapan (Today at 8:58 AM)

Thanks to all that assisted, 

one last question if I may.

I have this formula also working,
=if(D3="Pending",NOW(),"")

DateRoomTaskStatusCompletedfollow upNewPending13/01/2023 14:53:08Done

 How do I add a second conditions so when is also on status "done" also produce ,NOW() ?
something like 
=if(D3="Pending" or "Done",NOW(),"")
Because I try but I am not able to offer 2 conditions on teh formula

thanks in advance


----------



## Sufiyan97 (Today at 9:02 AM)

Try


```
=IF(OR(D3="Pending",D3="Done"),NOW(),"")
```


----------



## ricapan (Today at 9:28 AM)

I tried and gave me error: Wrong number of arguments to IF. Expected between 2 and 3 arguments, but received 1 arguments.


----------



## Sufiyan97 (Today at 10:13 AM)

Sorry not getting what you are trying to say

Can you send screenshot or mini sheet of error you are getting.


----------



## ricapan (Today at 10:30 AM)




----------



## Fluff (Today at 10:32 AM)

Are you doing this in Excel or Google Sheets?


----------



## Sufiyan97 (Today at 10:39 AM)

ricapan said:


> View attachment 82701



I think closing parenthesis is missing in your formula after "done"*)*


----------

