How to stop today function date changing every day in Excel Closed

Question

Hi, 

I am using this formula on my work sheet in cell (M7), and I have 13 sheets for each month in this workbook and I dragged the formula till cell (M29)

=IF(K7="closed",TODAY(),IF(K7="open","pending",""))

The problem is that when I open my sheet, the next day all the entry dates change to today. I need the previous day entrees to stay the same and only the new entry to show today's date. 

I am not professional in vb with excel .

Please help!

Haneen H · Accepted Answer

Hey everyone!

Thanks a lot for all the suggestions! Here is what I think I should conclude with:

I think it worked, but all the formulas I added to the cells are gone.

 If I enter "open"  in cell ( K7 ) the word "pending" will appear in cell ( M7 ).
 If I enter "closed"  in cell ( K7 ) the date of today will appear in cell ( M7 ).
 If nothing entered in cell ( K7 ) nothing will appear in cell (M7 )
 And so on to the rest of the cells .
 This is the formula I was using :

=IF(K7="closed",TODAY(),IF(K7="open","pending",""))

It worked for me. Hope, it will work for rest of you also!

Haneen H · Answer

Do you mean change the date of my computer ?

ac3mark · Answer

OK, hang in there......


Put this in the worksheet code of VBA.

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Application.Intersect(Target, [B5]) Is Nothing Then 
        [A5] = Date 
    End If 
End Sub 
 

The above will update the date in A5 only when item B5 has been changed.

I will expand to other ranges after you have published and tested this solution.

ac3mark · Answer

Do we ever have to check any other cells?
Do you need help with the logic of what is entered?


We can add the logic to see what is being typed, as in

If Target="Pending" then
DOSOMETHING()
end if
 
 
//ark
-Moderator/Contributor

Haneen H · Answer

thank you very much for trying to help me 
but  I really don't know what to do 

I need these logic in my workbook , and I'm really really not good in VB in excel .


I'm not sure that you get what  I want exactly

my only problem was with the date changing everyday I open the workbook.

is there any other function or formula that I can use easily in excel ???

I'm very sorry for bothering you , or if I look ungrateful to you .

How to stop today function date changing every day in Excel

5 responses

Similar discussions

Subscribe To Our Newsletter!