Timestamp date stays 12:00:00

[Johfra]

I have successfully found this formula:

=If (b1<>"",if(a1="",now(),a1),"")

It works BUT.....the time stays all the time to 12:00:00.

Yes, I have removed circular warning.

What am I missing here?

Thanks.

J.

[FDibbins]

Probably you need to recalc?

[ChemistB]

What do you get if you just put
=NOW() into a cell?
How is your cell formatted exactly?

[Johfra]

FDibbins - what should I be recalculating?

ChemistB - when I just put the "now", it shows the right time. My cell is also formatted to be time.

I'm stumped! LOL

[ChemistB]

You should have your Options>Formulas> set to iterations:1. That formula should be in A1, yes? Then when you enter something into B1, you'll see a time in A1. Is that how you have it set up?

See attachment

[joeu2004]

I have successfully found this formula:
=If (b1<>"",if(a1="",now(),a1),"")
It works BUT.....the time stays all the time to 12:00:00.

I assume the formula is in A1, and you selected the Iterative calculation option.

In that case, A1 will change only when first you clear B1 (A1 will appear blank), then you enter something into B1 (A1 should reflect the current date and time if it is formatted as m/dd/yyyy hh:mm:ss.000 or something similar).

If you are doing all that and you still have a problem, I suggest that you attach an Excel file that demonstrates the problem. The devil is in the details.

[Johfra]

I've done that, too...

[sktneer]

That formula will only work if you enable the iterative calculation as stated by ChemistB in post#5 and moreover this formula is normally used to populate a cell with static time when an entry is made in another cell. In your case A1 will only be populated with the Time value once you enter something in B1 and once B1 is not blank A1 will show you the time when a value was entered in B1 and it will not change automatically unless you delete the content of B1 and re-enter something again in B1 and this time A1 will show you the latest time value.

Hope this helps.

[Johfra]

I've attached the file.

Macro wasn't set up yet....pay no mind to that.

Everything I can think of checking, always ends up with the same result...12:00:00.

There's info (date) in the referred cell.

I also realize that the formula given was not the same cells as what I have here - I was just trying to keep it generic.

Thanks for your help everybody!!

[sktneer]

Do you understand what this formula is doing? If not please read the post#8.
I deleted the date in G3 and re-entered it and the time changed in G4. So the formula is doing exactly what it is supposed to do. Then where is the confusion?
Or I am missing something?

[Johfra]

Yeah - I understand all that. I'm figuring now its a problem on my end and not with me. I copied and pasted your formula and it still gives me 12:00:00 - despite doing all the abovementioned steps (which I had already done).

I understand what this function does - I'm not a newbie to Excel but, not all of us have a perfect understanding of all the ins and outs of Excel. Though I know quite a bit (more than average), I know what I'm doing.

I tried your sample and the time does not change - it may work on your end but not here. Gotta definitely be a glitch on my end.

Could that be it? Wouldn't be the first time..... :/

[joeu2004]

I've attached the file.
[....]
Everything I can think of checking, always ends up with the same result...12:00:00.

When I open the uploaded file, I see 0:00:00, not 12:00:00. Posting typo?

If you enter =IF(G3<>"",IF(G4="",NOW(),G4),"") into G4 when G3 already has a value, the result will indeed be 0:00:00, as it should be.

If you never clear G3, G4 will remain 0:00:00.

Again, you must clear G3 first, then enter something into G3 in order for G4 to change.

However, if Manual calculation is set in your instance of Excel, G4 still will not change.

Manual calculation is not set in the uploaded file. But it still might be set if Manual calculation was already set when you open the file in your instance of Excel.

[EDIT] PS.... Iteration calculation also will not be set if you open your uploaded file in an instance of Excel that already has an Excel file open with Iteration calculation deselected. But in that case, you would get a circular-reference warning when you open your uploaded file. Sound familiar?