Dates in excel are strange creatures.
They are not like calendars, but rather than numbers.
The creators of excel came up with the brilliant idea of starting from somewhere in time, then count the number of days.
That day (day one) was decided to be 01/01/1900 – So if I write this date in excel, and copy it as value, I will get 1.

1,2,3… and so on so forth. This might be O.K. for days, but how do you count a half day?

Parts of the days are expressed in decimals. They can go down to as small unit as a second. That’s how dates in excel are expressed.

For example the date: 23/01/2020 09:00 as a value looks like this in excel: 43853.375000

Date copied as value in excel
Copy and paste as value a date in excel

Let’s break down this number:

43853.375000

The first part: 43853 means that It is the 43853th day since 01/01/1900.

The second part is a decimal expression of the 9 hour if 24 was 1.

9/24=0,375000

Now to get you confused. There is another date system can be found in excel.
There is the one that starts at year 1900, and there is another one that starts at 1904.

But why?!?

It has to do something with the compatibility between Mac and windows. The old macintosh system uses a date system that starts in 1904.

How can I turn this 1904 system on?

You can do this in the options panel.

Time needed: 2 minutes.

 

  1. Go to file

  2. Go to Options

  3. Go to Advanced

  4. Then check option “Use 1904 date option”

     

Did you know that we have a facebook page? Come and visit us. If you give us a thumbs up it helps us to bring more and more excel content.
You can find more tricks like this in our General section.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]