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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    - 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):



    M N O P Q
    2 Energy Used Energy Left LVL UP? Last Fishing Time Energy Refilled Time
    442 5 4
    [2] 12:24 PM [3] 2:22 PM
    443
    [1] 63


    444 5 58


    445 5 53


    446 5 [1] 48 YES

    447 5 63


    448 5 58


    449 5 53



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

    Code:
    =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:

    Code:
    =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:

    Code:
    =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.

  2. #2
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,859
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

  3. #3
    New Member
    Join Date
    Aug 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    It worked!

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

  4. #4
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, Surrey
    Posts
    2,859
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    your welcome

    2nd link explains the same formula i a different way
    Wayne

    Using Excel Version 2013 (Have Version 2003, 2007 and 2010 available)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com