Formula doesn't work when copied

[Iwant2learnmore]

Hi everyone! I hope someone can help me out. I found a sample sheet online which in a way does what I wanted it to do. My problem is, the moment you edit the formula it no longer works as it should!

If you can see the image below, what I wanted to do is have the time/date appear in Column J whenever an employee enters his/her employee code in column B for his clock in and have the the time of his clock out appear in column K whenever he enters his/her code in column D.

Sample sheet.jpg

Below are the formulas I'm using

Original Formulas

(This formula is in column J of the workbook)
=IF(C5<>"",IF(J5="",NOW(),J5),"")

(This formula is in column C)
=IF(B5="","",IF(LEN(B5)>6,VALUE(LEFT(B5,LEN(B5)-1)),B5))

The iteration box is also ticked, but if I go to the formula bar and do something simple like place the cursor there and hit enter (even if I virtually DID NOTHING to change the formula) it will not work anymore! Why is that?

If I copy the formula down it works (as long as I am not copying it by highlighting the formula from the formula bar), but if I copy the formula and paste to a different workbook or worksheet it no longer works! Instead of showing the actual date and time it shows 12:00 AM and 01/00/00.

I hope somebody can help me.

[Sam Capricci]

that sounds like it is an array formula. If you put your cursor in the formula bar, before you leave it you have to hit control shift and enter to make it work. While it worked did it have {} at the front and end of the formula?

[Iwant2learnmore]

Hi Sambo Kid,

No, the formula is not enclosed in {} but I just tried your tip, hitting the ctrl shift enter keys and it worked! Thank you!

[Sam Capricci]

Great! Don't forget to mark your post as solved (using the thread tools dropdown at the top of the post) if that takes care of your issue.