Need help with Microsoft Excel problem

I have several versions of Office. Here is what I did with Office 2010.

Right-click on cell A1 and select Format Cells.
Select Date
Select desired format. In my case it was "March 14, 2001"
The cell should now be formatted correctly.

Select cell A3 and hit F4-key. That will format it the same as cell A1.



The formula in cell B2 should now work

got the test file, Denis. Thank you. Initial result is that the file opened in Excel without a problem, and reads just as you said. I appreciate the time and effort you've extended while trying to help.

Dinner calls (in a loud voice that sounds exactly like my wife), but I will try to get my files to react similarly to yours, later tonight.

Cipri, thanks for the heads up about SoftMaker FreeOffice 2024. Do you know what other Microsoft programs are supported by this suite (i.e., Word, Power Point, etc.)? Are there any compatibility issues with Microsoft Office 2003 (which is what I'm currently using) that you are aware of? I'm certainly going to try it out once I'm convinced that it won't screw up my current setup.

I'll get back to all of you later.

OK, I give up!
I've tried most of your suggestions, noe of which seem to work.
I've decided to attach the actual entire file that I'm have the problem with, in the hope that you might find what I'm obviously overlooking. *

I started this file to track ink usage (between cartridge changes) for one of my printers. Columns are self explanatory. There are 2 columns for each ink color, one for the date the new cartridge was installed, and one for the number of days elapsed the previous cartridge was installed.

As you can see, the program was working as it should (or as I think it should), right up until my most recent cartridge change (which I've highlighted in red).
Formatting for all the cartride color columns are the same, and formatting for all the elapsed time columns are the same, so there's no reason that I can identify for the sudden change in behavior.
Also, if you look at the fx window for the last date entry, you'll notice that it's different than it is for all the other entries.

I hope that you can find out what's causing this problem, and thank you all for taking your time to help.

* OOPS! My computer will not allow me to attach a file with an .XLS extension (and, probably, any executable file). I tried changing the extension t .X_S, but it nixed that, too. Can you tell me how to change this restriction so that I can actually attach the file?

LSF said:

* OOPS! My computer will not allow me to attach a file with an .XLS extension

Click to expand...

No, it's the forum that won't let you attach an .xls. Only a limited number of file types are allowed to be uploaded, image files, text files, and .zip archive files.

Right-click on the file, select Compress to... > ZIP File. This will compress your .xls file and put it into a .zip file with the same name as your spreadsheet. You'll be able to attach the .zip file.

Thanks, Bree.

I'm attaching the Zip file now.

LSF said:

Cipri, thanks for the heads up about SoftMaker FreeOffice 2024. Do you know what other Microsoft programs are supported by this suite (i.e., Word, Power Point, etc.)? Are there any compatibility issues with Microsoft Office 2003 (which is what I'm currently using) that you are aware of? I'm certainly going to try it out once I'm convinced that it won't screw up my current setup.

Click to expand...

Microsoft programs supported by this suite are Word, Power Point and Excel . There are not any compatibility issues with Microsoft Office 2003 that I am aware of.

You need to give them an email for they to send you an activate number.

I installed the following on my computer with Windows 7 Pro:

• Office Student and Teacher 2003
• Office 2003 Service Pack 3
• Compatibility Pack for Word, Excel, and PowerPoint 2007 Formats

I opened your Excel file, "Excel Issue (Actual File).xls", and got the same results:



I just happened to also have Office 2010 starter installed also. It had no problem with your file:

LSF said:

I've decided to attach the actual entire file that I'm have the problem with

Click to expand...

My Excel 2007 reads it all OK.


My cell A12 is formatted as a date.

LSF said:

if you look at the fx window for the last date entry, you'll notice that it's different than it is for all the other entries

Click to expand...

In your Excel 2003, what is shown in the formula bar when A12 is selected? It looks like it is pasted in as text in mine [the equivalents in A10, A8, ... all appear to be typed in as dates in their formula bars.

One of the others:-

[I believe my Excel2007 is recognising the content as being intended as a date & automatically treating it as such - just like that Planmaker application that somebody else used.]

---

By the way, there are several alternatives to MSOffice. None of them have an equivalent of MSOffice Outlook.
LibreOffice is pretty similar to old versions of MSOffice - see their sample screenshots.
LibreOffice - LibreOffice.Org
LibreOffice Portable - LibreOffice.Org
LibreOffice grew out of OpenOffice and is now said to be the better supported & more frequently updated one.
LibreOffice vs OpenOffice - LibreOffice.Org
about LibreOffice [Bree #2] - TenForums
LibreOffice vs OpenOffice [RickC #30] - TenForums


Denis

Update: I solved your problem. It was staring right at me.

I looked at Cell A12. There was a little green arrow indicating an error.



The date text in that cell, "8-Aug-26", had a 2-digit year. It seems that Excel 2003 expects a 4-digit year. Note all of the other cells have 4-digit years.

There are two ways to fix that error. Either

1. Change the year to 4 digits, or
2. Right-click on Cell A12 and select "Convert XX to 20XX"

Here I right-clicked on the cell and fixed it.



The formula in Cell B11 now works



The problem is with Excel 2003. Excel 2007 and newer don't have this problem. I can understand why Excel 2003 wanted a 4-digit year.
For example, for the text "8-Aug-26". Is the year 1926 or 2026? It appears that Excel 2007 and newer assume the year is 2026.

Cipri, thanks for he info. I might give SoftMaker FreeOffice 2024 a try, but I'd still like to understand exactly why Win11 has problems with the latest entry, but doesn't with the earlier entries. The earlier entries (through 22 April) still look like they work. I'm not exactly sure of the date when I switched to my new computer with Win 11 Pro from my old computer with Win 7 Pro, but either way, the entries worked as expected. You have shown that the new entries didn't work for you with Win 7. I guess I should try the file on my Win 7 computer to see what happens. Switching to Win 11 might be what's causing the problem.

MisterEd, the earlier entries may have been generated by either Win 7 or Win 11 (depending upon when I actually set up my new Win 11 computer). Both computers were set up with Office 2003 using Excel 2003. I'm not sure which OS I was using at the time these earlier entries were added. In any case, they
obviously worked on either computer prior to April of this year. Since I'm not going to invest in a later version of Microsoft office at this point, and SoftMaker FreeOffice 2024 looks like it might be a viable alternative, I'd still like to understand what changed. As I said above, my next step is to go back to my Win 7 computer and see if the problem still exists. It's a learning experience.

Please read my last post.
I think you created the entry in your A12 by pasting text.


Denis

MisterEd, you suggested

"The date text in that cell, "8-Aug-26", had a 2-digit year. It seems that Excel 2003 expects a 4-digit year. Note all of the other cells have 4-digit years.
There are two ways to fix that error. Either
1.Change the year to 4 digits, or
2.Right-click on Cell A12 and select "Convert XX to 20XX"
I tried that and it didn't work.
Note that all the dates cells prior to A12 only show 4 digit years in the fx box, not in the cells themselves. Therefore, when I enter the date in A12 in the same format as those cells above it, why does the fx box show a different format then it does for all the earlier cells?

LSF said:

Note that all the dates cells prior to A12 only show 4 digit years in the fx box, not in the cells themselves

Click to expand...

Sorry, that's the wrong way around.
What is in the formula bar [the "fx box"] is what is in the cells themselves.
What you see when you look at the cells themselves is a display of that content and is subject to whatever formatting has been applied.

I suggest you copy & paste cell A10 onto cell A12 [so that it is forced into the same format as the others] and then re-type the desired A12 entry, 8/8/2026
I do not know the threshold for applications assuming a two-digit date is in the 1900s or the 2000s. For my Excel 2007, it's a date during 2029.


Denis

Fx stands for Formulas not Special effects.


Denis

Bree - I can see you're still looking at this thread. I've just added to my post #33, "I do not know the threshold for applications assuming a two-digit date is in the 1900s or the 2000s. For my Excel 2007, it's a date during 2029.".


Denis

MisterEd said:

The problem is with Excel 2003. Excel 2007 and newer don't have this problem. I can understand why Excel 2003 wanted a 4-digit year.
For example, for the text "8-Aug-26". Is the year 1926 or 2026? It appears that Excel 2007 and newer assume the year is 2026.

Click to expand...

In Excel 2007 and above the error is indicated by the little green triangle at the top left of the cell when you click on it. A drop-down list of options appears.....

LSF said:

I'd still like to understand what changed.

Click to expand...

....this green error indicator is Excel's 'background error checking' at work. In Office 2007 and above this is enabled by default, and also seems to interpret the 2-digit date correctly (while still warning that there's an error).

In Office 2003 background error checking is off by default (presumably because it would have been slow on contemporary processors) but you can enable it.....



The difference between 2003 and 2007 onwards is that 2003 waits for you to choose between 19xx and 20xx years, while later versions automatically assume it's 20xx.

Try3 said:

Bree - I can see you're still looking at this thread. I've just added to my post #33, "I do not know the threshold for applications assuming a two-digit date is in the 1900s or the 2000s. For my Excel 2007, it's a date during 2029.".

Click to expand...

Yes, I was busy checking out how it behaves on my Office 2003 machine (not the fastest PC in my stable ).
Yes, it's somewhere in 2029 for Excel 2003 too, with background error checking on it will automatically convert the cell to 1929 without asking.