If statement to generate a timestamp
Results 1 to 5 of 5

Thread: If statement to generate a timestamp
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If statement to generate a timestamp

    Hello,
    I am fairly new to excel and the forum, so please excuse me if I am not following protocol.
    I am using Excel 2013 on a windows 8 machine.
    It may be easier for me to explain the problem, before posting about the formulas.
    I maintain a retirement residence with alot of phone numbers. I have a list of all the numbers we own in one column and the numbers assigned to the PBX systems in a second column. By using a
    If formula I can tell which numbers have not been assigned (The true statement).
    My problem is when a resident passes away, I need to reuse that "phone number", but I dont want to reuse the number too soon. I am hoping to use the "true" from the if statement to generate
    a time stamp. I plan on using that time stamp against the "today()" in a days formula, I believe this will spit out the number of days a number has been idle. The time stamp would be constantly changing or removed as the if statement would always be changing.
    So column C gives the True statement and says "Number not Assigned" - this should create a time stamp in Column W
    Any help I can get on this would be greatly appreciated.

    A B C U V W X
    All Numbers PBX numbers IF Formula Today() Time Stamp Days Formula
    5555555 True
    Number not Assigned
    2014-02-20 2014-02-16 4
    5555556 5555556 false
    5555556
    show nothing no time
    stamp
    0
    5555557 false
    5555557
    show
    nothing
    no time
    stamp
    0

  2. #2
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Wayne

    Using Excel Version 365 for Mac

  3. #3
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement to generate a timestamp

    Sheet1

     ABCDEFGH
    12All NumbersPBX numbersIF Formula  Today()Time StampDays Formula
    135555555 Number not Assigned  25/02/201420/02/20145
    1455555565555556      
    1555555575555557      

    Spreadsheet Formulas
    CellFormula
    C13=IF(B13="","Number not Assigned","")
    G13=IF(C13<>"",IF(G13="",NOW(),G13),"")
    H13=INT(F13-G13)+1
    C14=IF(B14="","Number not Assigned","")
    F14=IF(C14<>"",TODAY(),"")
    G14=IF(C14<>"",IF(G14="",NOW(),G14),"")
    C15=IF(B15="","Number not Assigned","")
    F15=IF(C15<>"",TODAY(),"")
    G15=IF(C15<>"",IF(G15="",NOW(),G15),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Wayne

    Using Excel Version 365 for Mac

  4. #4
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement to generate a timestamp

    Thanks for the quick reply Etaf ,
    I modified one formula to =IF(C2="Number not Assigned",IF(N2="",NOW(),N2),""), as every cell in column C was populated with something, but it seems to generate the time stamp.
    The other change I made was =IFERROR(INT(N2-O2)+1,""), as every cell that didn't have a time stamp gave #value. It is showing one day now, so I guess it is working.
    Thanks again for the excellent help

  5. #5
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement to generate a timestamp

    your welcome, glad you got it to work OK
    Wayne

    Using Excel Version 365 for Mac

Some videos you may like

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
  •