Solved An IF formula populates cell with a date of jan 0,1900

October 3, 2012 at 10:35:13
Specs: Windows XP
I hope you guys can help me figure this out.
My formula is
=IF(G20="","",IF(OR(H20={"",0}),TODAY(),H20))
I want H20 to stay a date when G20 had an "X" in it until G20 no longer has anything in that cell. The problem is that it returns with the Jan 0,1900 as today's date. I have all the transition settings turned off and when I just put =today() in the cell it comes back with the correct date. This means to me that I probably have an issue with my formula. Please help!!!

Thanks Kyle


See More: An IF formula populates cell with a date of jan 0,1900

Report •


#1
October 3, 2012 at 10:56:46
I want H20 to stay a date when G20 had an "X" in it until G20 no longer has anything in that cell.

Why such a complicated formula?

This seems to work for me in Cell H20: =IF(G20<>"x","",TODAY())

If G20 has anything but the letter X, H20 will remain blank,
if G20 has the letter X the H20 will display Today's Date.

Unless I'm missing something?

MIKE

http://www.skeptic.com/


Report •

#2
October 3, 2012 at 11:28:09
✔ Best Answer
Thank you Mike. your formula worked great. You're not missing anything. This was a formula that was given to me by a different forum and it worked for a little while until recently. So to answer your "Why so complicated?" question simply, I didn't know any better. :) Thank you for your help.

I have to say that this is one of the most helpful forums that I have come across with all the easy to follow threads and information.

Thanks again,
Kyle


Report •

#3
October 3, 2012 at 11:31:36
You can't use that formula in H20 because it references H20. That is going to cause a circular reference. You are asking Excel to produce a value in a cell based on the value in the cell itself. For obvious reasons, Excel can't do that.

A circular reference will sometimes return a 0 value. If the cell is formatted as a date, then the cell will return 1/0/1900 since that is the first day of the Excel calendar.

BTW...you may not realize that you have a Circular Reference because Excel will only tell you once. Once you clear the first Circular Reference warning, you can enter formulas with circular references all day long and Excel will never warn you again until you close and reopen the workbook.

I'm not 100% sure what you are trying to do, but I know that you can not use that formula in H20.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 3, 2012 at 11:48:22
derby dad,
That makes a lot of since now. I was using that formula in H20. Thank you for your help.

Kyle


Report •


Ask Question