Date Errors

[excel_8543]

I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:

=IF(D3="", IF(B3=";", FixedDate(),B3))

When I enter data into cell D, I get the following result in B:

0-Jan-00

Taking this a step further, even if I do a "control + ;" I still get a value of
0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??

I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.

Any thoughts?

Thanks!

[oldchippy]

Not sure I understand the question fully, but does this help - if not can you explain more

=IF(AND(B3="",D3=""),TEXT(39091,"mm/dd/yy"),B3)

[excel_8543]

Thanks for the reply! I pasted your formula into a blank sheet and I still get a returned date value of: 01/00/00.

Something is either missing or wrong with my Excel but this did work last year for me. What I'm looking to do is when data is entered into cell D3, that days date appears in cell B3. Now, If I open the sheet tomorrow, I still have yesterday's date in cell B3. If new data is entered into cell D4, I get that date in cell B4.

Thanks!!

[oldchippy]

What you need is a macro to record the date each time the data changes in cell D4, take a look at this link

http://www.cpearson.com/excel/events.htm

Hopefully someone with more vba knowledge than me will right the code for you

[excel_8543]

I attached a snap shot of what I get when I run the "control+;" command. I should get 01/09/2007 in the cell but I only get 0-Jan-00

[excel_8543]

I also tried this equation:

=IF(ISBLANK(D1),"",IF(B1="",FixedDate(),B1))

D = driving value. Once data is entered here, a fixed date is placed in B

B values are still returning at 0-Jan-00.

I used this FixedDate command from September 06 to Dec 06 without any issues. Once we got into Jan 07, it no longer works.

[oldchippy]

Is it possible to post a zipped spreadsheet (remove sensitive data) so that someone can see exactly what you mean?

If you go to My Documents or wherever you keep your file, right-click on it and Sent to > Compressed file, then post that.