If cell contains specific text, then return a value

[ricapan]

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]

new sheet

 

[ricapan]

What is your file extension

how do i look this?

 

[ricapan]

 

[shinigamilight]

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]

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 "=".

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]

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

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]

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]

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]

@shinigamilight, thanks a million, it works perfectly.

 

[ricapan]

I dont know how to close the thread