Securing today using “Today” function.

[ebeyert]

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

 

[Scott T]

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.

 

[ebeyert]

 

[ebeyert]

A

B

C

D

E

F

G

H

I

J

K

L

M

1

2

--

9

Activity

Status

Start date

Due Date

Date Closed

10

Action 1

Open

1 Dec

15 Dec

11

Action 2

Closed

2 Dec

13 Dec

12 Dec

12

Action 3

Cancelled

3 Dec

22 Dec

12 Dec

13

Action 4

Planned

18 Dec

30 Dec

14

Action 5

Open

4 Dec

25 Dec

15

----

1000

<tbody>
</tbody>

This is a example of my sheet. Thanks for the support.

 

[Jonmo1]

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()),"")

 

[Scott T]

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

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

 

[Scott T]

Forgot to add you will need to save the file as a macro enabled file type such as .XLSM

 

[ebeyert]

Scott T, Thanks.... Works!

 

[ebeyert]

Jonmo1, thanks for the feedback.