Adding in Excel Dates

[jodenice]

A guy in my office has a form

it has various headings - but the ones I am stuck on are below.

BADGE NAME DATE Issued

He has set up a basic vlookup so that typing in his badge number also will type out his name. However, he also wants the current date to come up every time he does this.

As the names and badge number don't change and have no difference from row to row, how can I get it to date stamp it with a different date each time?

I am stuck!

Thanks

[DonkeyOte]

Are the rows "re-used" ...

ie if badge number is entered in A2 and thus name is populated via VLOOKUP in B2 and current date is set in C2 -- would it be likely that someone would in future return to A2 and enter a new badge number (or repeat the old number) and in turn expect B2 & C2 to update accordingly ?

The traditional approach to answer your question is to use VBA to insert a static time stamp (assuming it should be generated automatically) -- however pending the answer to my question it's possible that you could avoid VBA using Iteration (thereby permitting use of Circular calculations)

[jodenice]

No I think its a long term record that will just continue down the page without re-using already populated cells.

[royUK]

Attach a small example workbook.

[DonkeyOte]

In the file that contains the form enable Iteration (Max Iteration = 1)

Pre XL07:

Tools -> Options -> Calculation Tab -- check Iteration -- set Max Iterations to 1

XL07 (using keyboard shortcuts)
ALT + T -> O
Go to Formulas -- check Enable Iterative Calculation and set Max Iterations to 1.

Assuming A2 is badge number and B2 is lookup formula then formula for C2:

=IF(AND(A2<>"",C2=0),TODAY(),C2)

You should find that when you enter a value into A2 -- C2 populates with current date... it won't change thereafter (ie is essentially static)

[jodenice]

Hi Thanks for this, I tried this and its coming up with zero but not sure if its looking at wrong cells

(a2) badge
(b2) name
(c2) title only, showing whether its the date issued or date returned
(d2) want to show static date

I have tried mucking about with the cell numbers and I changed the iteration as per the instructions.

[jodenice]

Example tatts form.xls

OK I am trying to attach a copy.

[DonkeyOte]

Formula for D5 (with iteration activated (note the file you uploaded does not have iteration enabled))

=IF(AND(A5<>"",D5=0),TODAY(),D5)

Copy down to D36

If you change A5 to 3140 you should find the date is populated in D5
(you may need to format D5:D36 to show Date as opposed to it's integer value)

[jodenice]

Hi

OK I can get it to change D5 date. However, regardless of whether there is anything in the badge number it shows todays date in all of the copied cells. Is there anyway to only show something when there is a number entered?


I do appreciate you taking the time to help me.

[jodenice]

hold on, just started again and now its showing as a date in the right place and 0/1/00 everywhere else which is good enough for me!

Thanks so much

[DonkeyOte]

OK.

Iterative Calcs are not normally advised but if using VBA is risky (in terms of distribution etc) then it's a decent workaround... if you can use VBA I would do so, let us know.

On an aside -- re: 0/1/00 (ie 0 integer formatted to date) -- if you wish to show as though it were blank use a custom format such as:

[=0]"";d/m/yy

although the underlying value persists (0 is in the cell) XL displays to end-user as though blank.