Date Entry shortcut bug?

[Gavona]

Pulling my hair out!
Windows region settings are English UK with short and long dates as day month year.
The cell format is DD-MM-YY HH:MM:SS

If I click on the time at the bottom right of my desktop the calender that comes up shows 1 December.
So windows knows the correct date.

I am using Office 365 Excel version 2102 (Build 13801.21050 Click-to-Run) Semi-Annual Enterprise Channel

I enter a date using Ctrl-;
or DateTime using Ctrl-; [space] Ctrl-:

The date is being interpreted as 12 January rather than 1st December

Re-format cell to long date - the sample date shown says 12 January 2021.

If instead of using the shortcut to enter date I enter =Today() or =Now() or 1/12/2021 everything works as it should.

This is a newish issue. Any ideas? Is it a known bug?

Thanks in advance for any inspiration, pointers etc.


Gavin

 

[SkipVought]

Hi,

What happens if you key in a cell...
[tt]1/12[/tt]
...and hit ENTER?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Gavona]

1st Dec but today Ctrl-; works as expected - even if I set the time zone back 12 hrs (I can't change the system date)

Gavin

 

[SkipVought]

1st Dec but today Ctrl-; works as expected

I am confused.

I thought you said that Ctrl-; works NOT as expected???

We were talking about entering Date/Time keyboard shortcuts but nothing about changing system dates.

So what do you mean by,

Ctrl-; works as expected

? Isn't that what you wanted to happen each time your used the Ctrl-; shortcut?

All that Ctrl-; does is puts certain characters into the selected cell. Happens to be that the characters are those corresponding to the current date. Those characters do not become a date until you hit ENTER, at which time Excel inspects the character string and determines that those characters represent a date and then begins to parse out the day, month and year and finally converts that result to a date/time SERIAL NUMBER equivalent and gives that cell display a Date/Time Format.

FAQ68-5827

BTW, today, 2 Dec 2021, the SERIAL numeric value is 44532.

So, in essence, Excel CHANGES the string, 1/12/2021 in the cell, to the number 44532.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Gavona]

Hi Skip, Sorry I missed the notification of a reply.
When I said "Ctrl-; works as expected" I indeed meant that there was no longer an issue. I understand everything you said. I hadn't thought to split the operation between the text entry portion and the parsing operation as I could not say I had observed that was how it operated.

On the 1st Dec:
[ul]
[li]Ctrl-; Enter was putting the wrong date into the cell. 12 Jan 21 rather than 1 Dec 21. So Excel was not using my region settings.[/li]
[li] Manually entering the date in dd/mm/yy format resulted in the correct date, suggesting that the parsing was working ok.[/li]
[li] However, as I cannot replicate the issue any more I can't confirm with 100% certainty whether Ctrl-; was putting the wrong characters into the cell or if the parsing that happened immediately after was faulty. I believe it was the first of these[/li]
[/ul]

On 2nd Dec: the problem seemed to have cleared. I will post here again if it comes back.

(I was wanting to change the system date in order to be able see if that could enable me to reliably replicate the problem.)

Thanks for your advice.


Gavin

 

[SkipVought]

Great! Thanks for clarifying.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!