Trying to get a cell to automatically input the time and date when scanned

So I am building a spreadsheet referencing serial numbers in another column when I scan a device the serial will check for that string of text and produce a value of 1 if the text is found.

I used the following formula to reference that output of 1 in the cell to the left of the date serial scanned at facility column. "=IF(($L$4=1),NOW(),("Not Scanned"))"

It works perfectly.

However when I scan the next serial number, it updates all of the dates and times that I have scanned each time it scans. I'm not entirely sure why.

Just wanted to see if there was something I was missing or another formula I could utilize to accomplish this task.

Thanks!

PS: I've attached screencaps for reference.

So looking at this information, would it be possible to also have the "Not Scanned" message appear by default in cells where the reference does not equal the value of 1?

Yes. Something like...
=IF(A1<>"",IF(B1="Not Scanned",NOW(),B1),"Not Scanned")

So I think I input the formula correctly, and I am not getting any errors, however It is not giving me the correct date and time once it calculates. It is giving me 1/0/00 12:00AM as a result. Not sure what is going on. I've attached screencaps for reference.Capture_03.JPGCapture_04.JPG

Appear to be getting incorrect date as result using this formula. Not sure why.
Capture_05.JPG

This worked! I just needed to recalculate the value in I4 so that the value in L4 would update!

Now I have a secondary problem of having to manually checking Enable iterative calculation checkbox everytime I open excel or send this document out for use.

There anyway I can enable it by default when the workbook is opened?

You'd need small piece of VBA I'd imagine. Applied to Workbook module, and tied to Workbook_Open event. Whomever that receives file must enable Macro (you can have additional layer set up to force this).
File must be saved as .xls, .xlsm or .xlsb format.
Ex:

Please Login or Register  to view this content.