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

Hi Palcouk,

I tried to enter it in "/" delimited format (dd/mmm/yyyy hh:ss) or even with "-", but it still isn't recognized as a date and time. It is however recognized if my entry is reversed, i.e. time and date.

Jumping off from my below thread with Madoc, it seems specific to Office 365 Proplus; I can confirm the problem's not there in Office 2010.

Thanks,

Bennet

Answer 3

Hi Bennet,

I'm so glad that you've found a solution to this behavior. I also did a test on my side based on your description, and the same situation did happen.

Thanks for sharing the method that it may help others who met the same scenario easily solve it.

Have a nice day!

Best regards,

Madoc