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.

 

[aaewalsh]

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

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

 

[ricapan]

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]

comma is missing after NOW()

 

[Fluff]

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]

Still not working, any ideas please

 

[aaewalsh]

Book1
	A	B	C	D	E	F	G
1		Date	Location	Task	Status	Pending	Completed
2					done		12/01/2023 09:52:00
3					new
4					pending
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(E2="done", NOW(),"")

 

[aaewalsh]

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

 

[ricapan]

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]

What is your file extension