Automatically fill in date in column A when column B contain words

man

New Member
Joined
Jul 26, 2010
Messages
37
I have column A to fill date, column B to fill in yes, column C is the description of task

Column C is filled with many rows of different tasks.

If i have completed a task listed in the row today, I will fill in the column A with today's date and put a "yes" in column B to indicate that it is done

Every I will need to fill in column A date and column B.

Is there a way that excel can automatically fill up column A date (by using the pc date) whenever the excel detects that column B is filled with "yes"? So in this way, I do not need to type in the date myself. As long as I type in yes in column B, it will have today's date automatically fill in in column A.

Thanks
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about this...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("B:B")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    If Target.Value = "Yes" Then Target.Offset(0, -1).Value = Time
End Sub

Place this code in the worksheet module
 
Upvote 0
I am new to this worksheet module thing, this is my first time applying a worksheet module, how should I use it?

Is it possible use a formula inside the cell to do what I wanted?

Thanks
 
Upvote 0
Please pick one forum thread or the other and stick with it.

Response found here and not the other.

I am new to this worksheet module thing, this is my first time applying a worksheet module, how should I use it?

Read here about where to place the code. Once you place the code in the worksheet module from there it is automatic when you add "Yes" in column B.

Is it possible use a formula inside the cell to do what I wanted?

This question was answered in the other thread and 40697 is a date. You need to apply a date format.

Please read here for some more info on dates
 
Upvote 0
I think this should help.

=IF(A2="yes",TODAY(),"")

I have applied a date format and it does show today's date. But this does not solve my problem. If I have done the work today, it will display today's date and I will click save and close the file. If I open the file tomorrow, the date will automatically change to the date where I open the file. It will always display the date that you are looking at the excel file.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top