Date Errors

**excel_8543** · 01-09-2007, 12:13 PM

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** · 01-09-2007, 12:35 PM

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** · 01-09-2007, 12:52 PM

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** · 01-09-2007, 12:59 PM

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** · 01-09-2007, 01:02 PM

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** · 01-09-2007, 05:54 PM

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** · 01-09-2007, 06:50 PM

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.

**excel_8543** · 01-10-2007, 09:22 AM

OK, here's a copy of what I'm working with. I really appreciate the support as I cannot figure this out. I still think it might be a problem within the settings because even a =NOW() or CTRL+; function returns 0-Jan-00.

**oldchippy** · 01-10-2007, 09:37 AM

When you open the zip file you get a circular reference error

Extract for help file
When a formula refers back to its own cell, either directly or indirectly, it is called a circular reference. Microsoft Excel cannot automatically calculate all open workbooks when one of them contains a circular reference. You can remove a circular reference, or you can have Excel calculate each cell involved in the circular reference once by using the results of the previous iteration. Unless you change the default settings for iteration, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first.

**excel_8543** · 01-10-2007, 09:50 AM

Yeah, I saw that, so I checked the iteration command under options. The thing I cannot figure out is that I did not get this error last year. The sheet worked fine from Sept.-Dec. 2006.

Even if you use just =NOW() you get the wrong value.

Thank you!!

**oldchippy** · 01-10-2007, 10:18 AM

When you say it worked Sept 06 - Dec 06, is this now another workbook? Have you made a copy?

**excel_8543** · 01-10-2007, 10:48 AM

Yes and no, I noticed in the original sheet that the date function stopped working as of 01-01-2007. So I created an exact duplicate and entered the same formula into the sheet.

Something is really weird here, I just went back to the original sheet and made a new entry, when I do this, I get a date value of 10-Jan-
07.

Yesterday this was not possible. The formula and option settings in this original exactly match the one I zipped in this forum. So how can one work and not the other? I attached a working copy of this sheet. password is set to: temp

**oldchippy** · 01-10-2007, 10:56 AM

Thought this what is was, you have a macro that does the FixedDate function. You can see it by right-clicking on the xl icon to the left of File, it's under Module 1, you need to copy this to your new workbook