How to stop "today" function from changing the date every day in excel

[Haneen H]

Hi,


Thanks in advance for you all


I've been looking and searching for a solution to my problem for an excel formula from the last week
but i did not find anything , I'm not that good in VB so I didn't know how to use the code ,


please please please pleas I need help






I have an excel sheet to track the customer Issues in my work


if the Issue is closed , I enter the word "closed" in cell "I7" and so on to cell "I29"
then the date will show in cell "K7" and so on in cell "K29"


and if the Issue is still open, I enter the word "open" in cell "I7" and so on to cell "I29"
then the word "pending" will show in cell "K7" and so on in cell "K29"


And if nothing entered in cell "I7" and so on to cell "I29" , nothing will show in cell "K7" and so on to cell "K29".


I am using this formula on my work sheet in cell ( K7 ) , and I have 13 sheets for each month in this workbook
and i dragged the formula till cell ( K29 )
=IF(I7="closed",today(),IF(I7="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, please help!!

 

[jsotola]

two ways

one ... use a workSheet_change macro to make date entries when specific cells are updated


two ... use a dropdown list for date value

 

[Haneen H]

I don't know how to use a worksheet_change macro to make date entries when specific cells are updated .

I do not understand the steps for VB with excel .. I am a little bit familiar with visual basic language
but I never used it with excel .

if I use a drop down list for date value I don't know if I would be able to show the "pending " word automatically when I enter "open" to another cell

or can I do that ?? with if function ????

like when I enter "Open" the word "pending" show in another cell , and when I enter "closed" the drop down list with date will show , and when nothing there ,nothing will show ????

can I do that ?

 

[Haneen H]

no one can answer me ?

 

[Drrellik]

I dont beleive you will be able to it without VBA.

 

[West Man]

Take a look at the method shown here. I have never used it so I do not know if the change in iterations might affect something else or not.
Create A Timestamp In Excel With Formulas

 

[Haneen H]

Thanks all for you reply ...


does anyone know a specific VBA code that look a little bit like what i want ? or where i can find codes to match my needs ?

 

[West Man]

Did you check out the link I provided?

 

[Haneen H]

yes I checked it out -D)


it's very useful ,, thanks a lot for this information .



It did work only if i have 1 (if)
if I joined 2 (if) only one will work every time , the other will work for one time only .




like *[ =IF(A1="closed",IF(B1="",TODAY(),B1),"") ]
the date will appear


and *[ =IF(A1="open","pending","") ]
the "Pending" will appear .


but when I joined them
*[ =IF(A1="open","pending",IF(A1="closed",IF(B1="",TODAY(),B1),"")) ]


it work ..but ....


when I enter "open" , "pending" will show
then I enter "closed" , the date will show


enter "open" again the formula will work


FOR NOW EVERYTHING IS GOOD


But when I enter "closed" for the second time , nothing happens ( the word "pending" will not changed )