Keeping Timestap/Time Static/Unchanging (Using IF, OR, and TIME Statements too)

[kitty86]

- Using Excel 2010
- All previous columns/rows are being used for something else and cannot be replaced/used for other information.


Hope my title is clear. Anyway, what I need help with is getting the time to stay static in two(?) columns, and not change unless the cell before it changed. Here is what my excel table looks like (part of it/mostly):


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Energy Used[/TD]
[TD]Energy Left[/TD]
[TD]LVL UP?[/TD]
[TD]Last Fishing Time[/TD]
[TD]Energy Refilled Time[/TD]
[/TR]
[TR]
[TD]442[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD][2] 12:24 PM[/TD]
[TD][3] 2:22 PM[/TD]
[/TR]
[TR]
[TD]443[/TD]
[TD]
[/TD]
[TD][1] 63[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]5[/TD]
[TD]58[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]445[/TD]
[TD]5[/TD]
[TD]53[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]446[/TD]
[TD]5[/TD]
[TD][1] 48[/TD]
[TD]YES[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]447[/TD]
[TD]5[/TD]
[TD]63[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]448[/TD]
[TD]5[/TD]
[TD]58[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]449[/TD]
[TD]5[/TD]
[TD]53[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

[1] : The formula I am using here/in that column (N) is:

=IF(OR(O443="YES", N442-M443<10),S1, N442-M443)

Explanation: If the sum of N442-M443 is below 10 or O443 reads "YES", it equals S1. S1, in this case, is 63 (Max energy). If neither are true (less then 10), just put in the sum of N442-M443. If one of the statements are true, it basically resets the energy level to max/whatever is listed inside of S1.
**NOTE: I understand that if I update S1, past cells with the formula will update too. I am not concerned with that. That is not too important. I just wrote it to save me time of resetting the time myself by hand for whatever cell at that time/moment. The formula I wrote works fine/the way I want it to.

[2] : For column P, the formula is:

=IF(N442<10,NOW(), "")

Explanation: If the N442 is below 10, put in the time it is now, otherwise leave it blank if it is false. The format of the time is "12:24 PM", which I set when I went to the format cell options.
**NOTE: This, and the other, is where I really need help with. The formula itself works, but as you know, the time does not stay the same. It changes each time I change any cell it seems and when I close/open the document etc. [3] will require the same help. Please view that too, as both are connected.

[3] : For column Q, the formula is:

=IF(P442<>"",(P442+TIME(0,SUM((S1-N442)*2),0)), "")

Explanation: If P442 is not blank, put in whatever is calculated in P442 and add time to it. In this case, adding minutes after finding the sum of 63(S1) - N442 x 2; otherwise leave it blank if it is false. The '2' represents every 2 minutes that passes, 1 energy is replenished. The format of the time is "2:22 PM". After I formatted the cell etc.
**NOTE: Like before, this is where I really need help with too. Since it connects to the previous column, the time will change here too. The formula itself works, but as you know, the time does not stay the same due to the previous column.

------------------------------------------------------------------------------------------​

Both P & Q columns are important because I need to know the 'exact' time energy will be full again. Having the time change on me is not what I want. I 'cannot' put it in manually because I constantly forget to put the time in as soon as I finish, as I become engrossed with my game or with something else, and because I plain forget. So the shortcut by hand is not helpful to me either. Also, I do not want it to change because I want to know what I did during that time frame etc.

So...that is pretty much my trouble. I am guessing some sort of macro is needed, but I have no idea how to use it or even know what to write. I would greatly appreciate the help/explanation. If there is a easier way to do this/the above, please feel free to share.

If you are that curious what the excel sheet/workbook is for, it is for the game/app 'Fishing Superstars' by Gamevil (for personal use). Meant to be a simple excel sheet thing at first, then I got engrossed into it too and it became the monster it is now. I'm still having fun learning either way, and I'm sure this sheet will be useful for future stuff too after some modifications are made.

 

[etaf]

this may work for you its setting a timestamp
you could combine with the IF conditions you want to set

Create A Timestamp In Excel With Formulas
How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online

 

[kitty86]

It worked!

Tried only the first link, but it worked. It didn't mess with anything else either! Thank you!

 

[etaf]

your welcome

2nd link explains the same formula i a different way