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

1. ## 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

3. ## Re: If statement to generate a timestamp

Sheet1

 A B C D E F G H 12 All Numbers PBX numbers IF Formula Today() Time Stamp Days Formula 13 5555555 Number not Assigned 25/02/2014 20/02/2014 5 14 5555556 5555556 15 5555557 5555557

 Cell Formula 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

4. ## 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

## User Tag List

#### Posting Permissions

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