O365 - Date not being seen as a number

[Vlad717]

I've had this happen on two machines and I can't figure out if it is a setting somewhere that was mistakenly turned off at some point.

Normally if you enter 08/09/2019 into a cell and press enter, excel will automatically see it as a number. We can help this along by setting the format to that matching type and entering the same thing.

My problem

1. Enter 08/09/2019 into cell A1
2. In cell B1 enter =IsNumber(A1)
3. Result should be True, but it comes up as False instead

Automatic formula calculation is turned on.
Formatting is set to date for the cell.
Fresh reboot with no other excel process running

Any ideas what the trouble could be?

Thanks,

[FDibbins]

Check to see if there is a leading space when entering that date
Are you positive the format is date?

[Vlad717]

Positive. There is no leading space either.

It started off with certain workbooks failing and I traced it back to the columns of dates. You could open these workbooks on just about all machines and they work fine. This one, just sees the date column as a text value.

That's when I tried to see if it was a number at all and it comes back with false.

That test I wrote in the first post should result in True. Fresh boot up of excel, type in a standard date in A1 and see if that value is a number. Any version of excel I tried results in True, except this machine and it has me stumped.

[FDibbins]

What happens if you F2 ENTER on that cell?

[Vlad717]

I tried that, it that doesn't force it either. I also tried copy/pasting it into a different spot, setting format to date and pasting it back.

[FDibbins]

Is it only in certain cells/sheets/files?

[Vlad717]

No, its any workbook on this PC. The example formula I posted is based off simply opening excel and in Sheet 1 typing in a date.

[AliGW]

No problem here in Excel 365 - sounds like a local issue. Have you rebooted the machine? Have you tried a repair of Excel?

[Vlad717]

Definitely rebooted the machine. Looks like I'll have to give repair a shot. Just wanted to make sure I wasn't overlooking something before hand.

[jason.b75]

Is the date correct in line with your regional settings?

If your pc is set to use the Canadian standard format of yyyy-mm-dd then your pc probably doesn't recognise mm/dd/yyyy or dd/mm/yyyy dates as a valid format.

Note that this is a windows setting, not an excel setting.

[CK76]

Canadian standard is dd/mm/yyyy Though I enforce US standard on most company systems (due to most vendor software exporting dates in mm/dd/yyyy format.)

But yyyy-mm-dd is universally accepted (even when short form is set to dd/mm/yyyyy).

[jason.b75]

Canadian standard is dd/mm/yyyy

Wikipedia let me down again

[Vlad717]

Haha, yes we do love to interchange our date format. That is a good catch on the regional settings. In this case I don't believe it would be the culprit though. Reason I believe that is 08/09/2019 would be a valid date in either format (mm-dd or dd-mm). While it would give the wrong value, the trouble here is that excel simply says it flat out isn't a serial date, which it should. Even if it thought it was August 9th or September 8th.

[AliGW]

Can you attach a workbook showing the issue? Just so that we can check that it is just on your system and not something in the workbook itself.

[jason.b75]

What happens if you enter a date in yyyy-mm-dd format? Does it accept that one?

What about =TODAY() formatted as short date?

[Vlad717]

For sure. Here is the sequence of events

1. Opened new workbook
2. Select A1 and change format from General to Date
3. Type in 09/08/2019
4. Cell B2 type in ISNUMBER(A1)
5. Results is False

Now if I was to do this on my computer

1. Opened new workbook
2. Select A1 and leave format as general (same result if I change it first to date)
3. Type in 09/08/2019
4. Date automatically changes to 9/8/2019
5. Cell B2 type in ISNUMBER(A1)
6. Results is True

Now on the buggered computer, I also tried it without the preceding 0 just in case and same result.

1. Opened new workbook
2. Select A1 and change format from General to Date
3. Type in 9/8/2019
4. Cell B2 type in ISNUMBER(A1)
5. Results is False
   
   Workbook is also attached

picturetest.jpg

[AliGW]

Do you have calculations set to manual instead of automatic?

Formulas > Calculation Options

Check your defaults on the 'buggered' PC.

[Vlad717]

I've found a third computer that does this. It makes me think that there is a setting somewhere in excel or windows that could be causing this.

[Vlad717]

Calculations are set to auto. I have a few macros that run so it was one of the first things checked just in case I forgot to turn it back on at some point. The third machine I just located with this quirk has never had any VBA run on it, the fellow rarely is in excel.

[AliGW]

Well, the reason I asked is that it was the same here when I opened the workbook, but clicking in the cell and ENTER rectified it. I was also able to add a date and it worked perfectly in the cell next to it.

[Vlad717]

Yup. When I open that workbook on my PC and press enter it is rectified and changes to True.

[jason.b75]

When I open the workbook attached to post #16, the date in A1 is text, F2 enter converts it to a proper date.

I'm still convinced that it's regional settings.

Go to the vba editor, press 'ctrl g' to open the 'immediate' pane, then copy and paste the line below and press enter. Is the result 0, 1 or 2?

Please Login or Register  to view this content.

[CK76]

Hmm, try checking Personal.xlsb file located in C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTATRT.

Other things you can check...
1. File -> Options -> Language. See Editing languages. If it has something other than English (Canada) or English (US) in there.
2. Check that these computers didn't accidentally have French keyboard turned on I doubt this to be the case, but could happen.
3. Start Excel in Safe mode. And see if it still has same issue.

[Vlad717]

When I open the workbook attached to post #16, the date in A1 is text, F2 enter converts it to a proper date.

I'm still convinced that it's regional settings.

Go to the vba editor, press 'ctrl g' to open the 'immediate' pane, then copy and paste the line below and press enter. Is the result 0, 1 or 2?

Please Login or Register  to view this content.

I really hope it is! The result I get is 2

[Vlad717]

Hmm, try checking Personal.xlsb file located in C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTATRT.

Other things you can check...
1. File -> Options -> Language. See Editing languages. If it has something other than English (Canada) or English (US) in there.
2. Check that these computers didn't accidentally have French keyboard turned on I doubt this to be the case, but could happen.
3. Start Excel in Safe mode. And see if it still has same issue.

No file in XLSTART. I think its normally empty by default though?

"English (United States) (default)" is selected

Same results in Safe mode

[jason.b75]

The result I get is 2

That means that your default date setting is year-month-day so anything else would be recognised as text.
Quoted from Microsoft help.

Order of date elements: 0 = month-day-year, 1 = day-month-year, 2 = year-month-day

You will probably find that entering 08/09/2019 as 08/09/19 (2 digit year) will work, but it will be 19 September 2008 rather than 09 August 2019 or 08 September 2019 depending on which format you thought you were using.

[Vlad717]

Oh, I'm feeling good about this. I have 0 as a result on mine. I'll update once its changed@

[CK76]

...Same results in Safe mode

Then most likely culprit is Region setting (date, time or number formats) as jason pointed out.

[Vlad717]

You guys are fantastic. It was the regional settings. It was set to Y/M/D which is why it wasn't interchangeable. Everything works now as it should. Thank you!!

[CK76]

Glad you got it solved.
FYI - There may be benefit to talking with your IT department. Typically, region setting should be standardized across the company network (using Group Policy etc).

If your issue is resolved, please mark the thread as solved by using Thread tools found at top of your initial post.

[Vlad717]

Trouble is our help desk is in the states and a problem with excel which is otherwise working is pretty low on the priority scale. Ticket has been in que since last thursday and finally got looked at today when I escalated it. I will mention what the result was though as they normally image the PC's when shipped so its rather odd there is a few with different date formats.

[AliGW]

Glad it’s sorted. Rather frustrating for you!