1900/1/1

Ooooo Maaaai, I had a ridiculous mistake in the recent post of using MS-Excel. I wrote in computers only date from 1970/1/1 could be calculated(converted to numbers), this was wrong, of course. The available data must be much more – 1900/1/1 was the date as the lower limit.

I confess it was not mere a wrong typing. As I was not sure about the exact lower limit of the available date data, I made a quick search, and, maybe selected a document about some different discussion.
What was really wrong with me was, I did not confirm it on the spreadsheet. Had I only a will to do that, there was a simplest way.

Input in any cell “1”.


At the moment you input it, the number “1” is aligned at the left of the cell. It means this “1” is assumed by the computer as mere a “letter”.

Confirm the input by pressing Enter key, so this “1” is shifted to the right. It means this is now assumed as a “number,” which can be compared or calculated with other number.

Namely, if you input number in any cell, Excel automatically treat it as “not only a letter, but a number, which expresses the amount to be sorted or calculated.” For example “1 was input. this 1 is bigger than 0 and smaller than2. and two 1’s make 2.”
This is indeed a great work of a computer, I think. For example, a goldfish would not assume that.

Then right-click the cell of “1” – excuse me, I use Excel in Japanese environment – and from the popup menu select “Format Cells”. The figure below means it in Japanese.

A dialog box appears. In the list on the left side, “General” is possibly selected, which means Excel assumes automatically, 1 as number.

Select “Date” instead of it, Excel converts a number to the count for a date from a given date.

By pressing OK, the cell, in which 1 was input, is converted to the date, 1900/1/1.

It means 1900/1/1 is counted as 1.

The what about the date counted as 0?
You can already see the result in the dialog box. input “0” in any cell, right-click to show pop-up menu…and in the dialog box the date which corresponds to “0” appears(in the red rectangle).

“1900/1/0″….a buggy result.

Then what about the day an year after, 1901/1/1?

It is to format the cell reversely. First input to any cell “1901/1/1”.

Right-click it and select “Format Cells” from the pop-up menu to show the dialog box. This time, the format in the left-hand list is set as “Date”, then select “General”.
In the dialog box, we can see the number for the date…

OH?

(To be continued)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s