• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to make static time stamp in excel

Hi Excel Experts,

I am trying to track my card swiped data with time stamp but every time when i try to insert a new data all the time stamp in the column updates to the most recent time.
What formula should i use.
I am using the following
=IF(C8="","",IF(B8="",NOW(),B8))

Another condition required is, if the member id, under column D is reflected for more than 3 times it will prompt an error message "Keep a look out for guest"
upload_2018-7-16_20-54-47.png
 

Attachments

  • Comp Alcohol Tracking with IT.xlsx
    17.3 KB · Views: 4
For a static timestamp, I just use shortcut key (CTRL+SHIFT+Semicolon). Or use small piece of VBA.

For a method using circular reference (iterative calculation) and formula. Refer to below article.
https://chandoo.org/wp/timestamps-excel-formula-help/

As for your second question. Use custom data validation. I assumed you meant Column B, as Membership ID is in that column and not D.

Select B3, use following validation formula.
=COUNTIF($B$3:$B3,B3)<4

Set Error Alert...
upload_2018-7-16_13-29-43.png

Copy validation to rest of the cells in range.
 
Hi Chihiro,

I have managed the problems but now the file size is too big if i copy to 30 worksheets, takes ages to open the file. Even if file is converted to xlsb binary format

Any solution for this
 

Attachments

  • Alcoholic Bev Tracking.rar
    187.6 KB · Views: 3
Your issue, is mainly unnecessary formatting applied to range outside of work area.

If you hit CTRL + END in your sheets (1 & 2). You will notice that it jumps to last available row (1048576). You could clean it up, using Home ribbon -> Editing -> Clear -> Clear All, while having range outside of your work area selected.

However, you may want to copy work area to another workbook and go from there. As clearing that large of range can take a lot of system resource.

See attached for cleaned up version of your file.
 

Attachments

  • Alcoholic Bev Tracking_rev.xlsb
    427.7 KB · Views: 4
Back
Top