2

After finding nothing via searches here, and more out of curiosity than necessity, I've observed the following, using MS Excel 2010 on WIN7 PC.

A Number-formatted alpha-space-numeric cell-entry such as

4 P

(no equals or plus sign; but with a space)

becomes, on hitting Return:

0.666… 

Similarly,

1 A

results in

0.041666…

But there is no similar conversion for some other alpha-space-numeric combinations such as:

2 T
or
7 K

So there doesn't appear to be a general conversion process involved.

On the other hand, if the cell was formatted General, it converts

4 P to “4.00 PM” and automatically changes the cell format to Custom.

I can see the sense in this, but can find no indication of why or how it would convert “4 P” or "1 A", but not some other alpha-space-numeric combinations to a number.

Assuming this occurs on other people's machines, can anyone explain what's going on here, please?

1

What you seem to be unaware of, is that there is no such thing as a "real" datetime in Excel. Note that dates and times are just a (part of a) datetime. Datetimes are actually stored as numbers.

To actually get datetimes to work, when you type in something that looks like a datetime to Excel it will, most times, convert it to the proper number representation. If the cell format is Text, Excel will not convert it.

If the format is Number, the converted number will be displayed as a number. If it's a date format, a date will be displayed. If it's General, Excel will also update the format as well as converting it to a number.

So when you type in 4 p (note that lower case also works), unless the format is Text, it is always converted to 0.666…, but what is shown depends to the format of the cell.

The datetime number itself is the number of days since 0 Jan 1900. The fractional part of one day, or in other words the time. Thus 4 p4 PM16/240.666… and 1 a1 AM1/240.041666….

Technically speaking, even though the cell shows 4:00 PM it actually contains 0 Jan 1900, 4:00PM, but Excel was "smart" enough to change the format to a custom time-only one.

  • I think you're off by one day. This answer mentions Jan 0, not Jan 1. I guess "smart enough" may mean that if a positive number is less than 1 then Excel doesn't associate it with any particular date, because Jan 0 is not a valid date in the real world. – Kamil Maciorowski Jun 11 '18 at 14:55
  • @KamilMaciorowski Thanks. I was just going by memory and didn't bother to check. However, It turns out that Excel, aka Micro$oft, are smarter than the rest of us. Apparently Jan 0 is a real date and will be displayed if a positive number less than 1, or 0, is entered into a cell. – robinCTS Jun 11 '18 at 15:32
  • Thank you for the explanation, All I wanted to do was place "1 P" in a cell and not have it transformed into something else. I was unaware of the existence of datetime (I use "Ctrl ;" for date, but might find a use for datetime). Meanwhile, for "1 P" shall have to use an extra keystroke. – iSeeker Jun 11 '18 at 17:08
  • @iSeeker There's actually 3 ways of entering 1 P in a cell. 1) By typing 1 P in a cell formatted as text; 2) By typing '1 P (which is what I guess you are referring to); and 3) By typing ="1 P" (three extra keystroke). Which one is best to use depends on what you are doing with the data afterwards. Most of the time, the second method works just fine. PS Don't forget to accept/unvote if appropriate ;-) – robinCTS Jun 11 '18 at 17:15
  • Yes - as you guessed, I do use the 2nd method. Thanks . – iSeeker Jun 11 '18 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.