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

Report
Posts
4
Registration date
Tuesday December 17, 2013
Status
Member
Last seen
December 18, 2013
-
Klaas86
Posts
1
Registration date
Monday August 29, 2016
Status
Member
Last seen
August 29, 2016
-
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!

5 replies

Posts
4
Registration date
Tuesday December 17, 2013
Status
Member
Last seen
December 18, 2013

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!
18
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4095 users have said thank you to us this month


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.




Klaas86
Posts
1
Registration date
Monday August 29, 2016
Status
Member
Last seen
August 29, 2016

Dear Mark, can you make the VBA code above work for two different pairs of cells? I have got a sheet with two dates. One date has to be created when a pre selected cell has been filled in, and the other date has to be created when another cell has been filled in.

With kind regards,

Klaas
Posts
4
Registration date
Tuesday December 17, 2013
Status
Member
Last seen
December 18, 2013

Do you mean change the date of my computer ?
nisva
Posts
233
Registration date
Friday December 13, 2013
Status
Member
Last seen
October 22, 2014
49
Ya to yesterday I think that would change the date in excel to.
Blocked Profile
@haneen, the date and time NEED to be correct on your machine is critical to safe operation! Please do not hack your date and time to pefrom this function!
Yes I use the word hack, because you would be changing something in your machine to circumvent the way the software reacts....HACKING!

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
Posts
4
Registration date
Tuesday December 17, 2013
Status
Member
Last seen
December 18, 2013

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 .
Blocked Profile
:)

You are not bothering me! I am sorry that I cannot communicate the answer. :(

I have built a sheet, and protecting the sheet doesn't lock the cell. So, no help.

The only thing we can do is think about how you need it to function.
So, we know that the VBA code works, so let us apply it to a new model.

Given:
cell stores the current days date (a1) that wil change ever time you open it...
we find the row that you are entering any status on and store it....
we find what status, if "open" then we add 2 to the cell that was the target and post "pending"
we find what status, if "closed" then we add 2 to the cell that was the target and post the value in a1


Is this what you want to happen? I know it is not how it works now, but I want to know if the logic is right!

please post back.....