Securing today using “Today” function.

E

ebeyert

Active Member
Joined
Sep 15, 2006
Messages
287
Use: Excel 2013

I have a formula in cell L10 which will enter today's date if “closed” or “canceled” is chosen in cell F10.

Example formula in cell L10: =IF(OR($F10={"Closed","Cancelled"}),TODAY(),"")

Formula works ok, expect: if I open the Excel sheet the next day, that day will be noted, etc, etc.

What I want is that at the moment (Day X) “closed” or “cancelled” is selected, that then that day (day X) is selected and that it will then no longer be adjusted.

Can someone assist me with this please?
Thanks
Best regards
Ellerd
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
You will need VBA to do this. I assume it is multiple cells not just F10 that could trigger the timestamp. Can you provide a sample of your data.
 
Upvote 0
login
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Activity [/TD]
[TD][/TD]
[TD][/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Start date[/TD]
[TD]Due Date[/TD]
[TD]Date Closed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Action 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Open[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 Dec[/TD]
[TD]15 Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]Action 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Closed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2 Dec [/TD]
[TD]13 Dec[/TD]
[TD]12 Dec[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Action 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3 Dec[/TD]
[TD]22 Dec [/TD]
[TD]12 Dec[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]Action 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Planned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18 Dec[/TD]
[TD]30 Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]Action 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Open[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4 Dec[/TD]
[TD]25 Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is a example of my sheet. Thanks for the support.
 
Last edited:
Upvote 0
You could use a Circular Reference for this.
Go to File - Options - Formulas
Check "Enable iterative calculation"

Make sure the conditions of the IF are FALSE (F10 does not = closed or cancelled) before you enter the formula.
Or cycle it from True to False back to True.

Assuming you put the formula in G10
=IF(OR($F10={"Closed","Cancelled"}),IF(ISNUMBER($G10),$G10,NOW()),"")
 
Upvote 0
This will check what is in column F any time cell in F10:F last row of data in F is change. If it is Closed or Cancelled it will put the date in L

Right click on the tab where your data is.

select view code
copy the code below into the sheet module

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
Dim trow As Long
lr = Cells(Rows.Count, "F").End(xlUp).Row
trow = Target.Row
If Not Intersect(Target, Range("F10:F" & lr)) Is Nothing And (UCase(Range("F" & trow)) = "CLOSED" Or UCase(Range("F" & trow)) = "CANCELLED") Then
Range("L" & trow) = Date
End If

End Sub
 
Upvote 0
Forgot to add you will need to save the file as a macro enabled file type such as .XLSM
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
L
  • Question Question
Macro stopped working - no longer selects worksheet
Replies
1
Views
694
NdNoviceHlp
NdNoviceHlp
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
234
Tarkemelion
T
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
900
Alex Blakenburg
A

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

Share this page

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