Excel 365: After 01 Oct 2019 update, excel does not recognize date and time in one cell as date and time

Question

Hi,

I have a timestamping worksheet that I use to log worked hours per task. Sometimes, I would need to enter the date and time manually in one cell rather than rely on the formulas. in the past, excel recognized it as date and time so the calculations in other columns worked fine.

However, after the update last 01 Oct 2019, this seems to have been broken; excel 365 now formats a manual date and time entry (dd mm yyyy hh:mm) as text, thus the calculations would not work.

I've done some hours of research and the few suggested workarounds (e.g. text to columns) would not work for me.

Is there an option somewhere to restore the old behavior?

As I'm typing this I discovered that manual entry in this format (hh:mm dd mmm yyyy) is recognized by excel as time and date, so if there are no better options, I just might have to shift to this mode of entry (and break a 6-year old habit of doing it the other way around).

I attached an annotated screenshot, below:

Thanks,

Bennet

[PII is masked by Madoc Huang MSFT]

Answer 1

Hi all,

After some more fiddling around it seems I stumbled upon the cause/solution - Windows Regional Settings.

My regional settings while I encounter the problem are these:

I note that in this case, Excel seems to store the date/time value in space delimited format. When you try to edit in the formula bar directly (or via F2), excel wouldn't recognize the result as date/time.

However, changing the regional settings to dd/mm/yyyy seems to fix this:

After this setting change, Excel would now recognize date and time in one cell however I input it, i.e.,

dd mmm yyyy  {time}

dd/mmm/yyyy {time}

dd-mmm-yyyy {time}

except in this format:

dd mm yyyy {time}

It seems the underlying reason for my initial problem is that Excel doesn't recognize input in that format, and my old regional settings forces date/time data to store in that format.

Odd behavior but for now I can consider my issue solved.

Thanks all!

Bennet

[PII is masked by Madoc Huang MSFT]

Answer 2

I tested under 365 Bus

For a cell using custom format, dd/mm/yyyy hh:mm resulted in that shown in your example R11C4 (i entered date and time)

Answer 3

Hi Bennet,

I did a test on my side on Excel with the latest version of 4 channels, but I cannot reproduce the problem you met.

No matter I enter (dd mm yyyy hh:mm) or (hh:mm dd mmm yyyy), the value would automatically change to (m/d/yyyy h:mm). And the cells can calculate properly.

If I’d like the entered values in my test workbook to show up like you, I need to customize the cells.

![](https://learn-attachment.microsoft.com/api/attachments/89f5dd77-51fb-414a-9a2e-0393131c3380?platform=QnA"2">

Answer 4

Thanks Madoc,

I think the issue seems to be version specific (on that note, I may have miscategorized this under Office 365 Business).

I recall the version at my work console is Office 365 ProPlus. I'll post the version and build number once I get back to my office.

On the other hand, on my home laptop, i'm using MSO Professional Plus 2010, and the problem does not seem to occur here on the same exact file. In any event, I'll PM you a clean version of the file so you could check.

Appreciate the assist.

Bennet

Answer 5

Hi Madoc,

I confirm that my work console is using Office 365 Proplus:

Just tested the exact file I sent you last night on this version, and the problem persists. On the other hand, the problem is not there in Excel 2010.

Regards,

Bennet

[PII is masked by Madoc Huang MSFT]