Excel Unplugged

Why Excel thinks “Time is on my side” by the Stones is a joke on him…

Excel has quite a few funny perks. Once you get over the 15 digit limit in Excel and the fact it does not calculate exactly as it should, you will sooner or later arrive at the point where you will feel the need to calculate time. In this case time stands for time and date formats in Excel. This is where things can get really strange. First we will look at dates and then hours.

Dates

01/01/1900 or 01/01/1904

The two dates are actually two modes in which Excel can deal with time. In the first mode (01/01/1900), which is also a default mode on a Windows computer, Excel starts counting the days from 01/01/1900. That day has number 1 behind it. And every other date has a number behind it also. For example, 04/13/1948 has 17 636 behind it. That only tells us, that 04/13/1948 was 17 636th day after 01/01/1900. But on Apple computers, the first date by default is 01/01/1904. So number 1 means 01/01/1904. Now imagine you are sending a file that has 01/02/1900 in a cell. All that Excel knows about that cell, is that the value of a cell is 2 and the format is Date. So when someone on a Mac will open it, he will get…

You guessed it, 2.1.1904. And this four year leap is repeated across all dates in your workbook. Another reason, 1.1.1900 might not have been the best idea to start with, is the 02/29/1900, which will be covered in the “Feb 29th 1900 was a Wednesday” section. Now to overcome all this or better yet to switch between the 01/01/1900 and 01/01/1904 modes you can go to FILE/OPTIONS/Advanced and then in the “When calculating this workbook:” section you select the “Use 1904 date system”.

Time in Excel

Feb 29th 1900 was a Wednesday

In this case Lotus is to blame and not Microsoft. They only adopted the solution (it would be better said adopted a problem, but as John Lennon says “There’s no problems, only solutions”). But the problem is there nonetheless. So what is the problem? Well, 02/29/1900 in reality did not exist, but in Excel (using the 01/01/1900 mode) you can write it and even see what day it was J

Time in Excel

It was obviously WednesdayJ. The problem is, that 02/28/1900 returns Tuesday and 03/01/1900 obviously Thursday, but in reality, they were consecutive days. So if you are looking for a difference in days between 01/01/1900 and 04/13/1948 you will get one day too many, since Excel thinks that 29/02/1900 was day #60 from the 01/01/1900 but in reality 03/01/1900 was.

=DATEDIF()

One more thing that Lotus brought along. The DATEDIF function is very interesting since it officially never existed in Excel (not counting the accidental mention in the help file of Excel 2000). But despite its nonexistence, you can use it to calculate the difference between two dates in Excel.

Basically you do something like =DATEDIF(Date1,Date that occurred after date1,”y”) and you get how many years passed between the two dates. The last argument can be “m” for months, “d” for days or “ym” for the remaining months over the calculated years and “md” for the days over the last calculated month.

For instance, to calculate the difference between 04/13/1948 and 12/31/2000 in years, months and days, one would do

=DATEDIF(04/13/1948,12/31/2000,”y”)&” Years “& DATEDIF(04/13/1948,12/31/2000,”ym”)&” months “&DATEDIF(04/13/1948,12/31/2000,”md”)&” days.”.

Time

Now time in Excel is just a decimal number that can tell you what percentage of the day has already passed.

Time in Excel

So adding time should be very easy.

Adding time

Adding time is very natural. Let’s say it’s been a slow week and you only worked 12:23 on Monday, 14:53 on Tuesday and 11:28 on Wednesday. Now you wish to add those up. So you do a SUM of those three values

Time in Excel

and you get

Time in Excel

The result should be 38:44 but instead we get 14:44. So what’s happening? Well like we said before, time is actually a decimal number for Excel. So in the first three cells we have

Time in Excel

And when we add those values up, we get

Time in Excel

Which is quite logical, but we want to see the result as time, so we change the format and get

Time in Excel

Well it’s 14:44 again. Now the value in a cell is 1.6138…, and Excel strictly speaking understands this as 14:44 on 01/01/1900. But if you want for hours to go over 24, you must use the following Custom Format [h]:mm. As you can see in the image bellow, this drastically changes the way we see the output

Time in Excel

So now we can add time. But how about subtracting time?

Subtracting time

This is an even bigger conundrum. Let’s say we got the following time values and formula.

Time in Excel

Pretty straightforward right? Here’s the result

Time in Excel

You get an endless ####… So how can you subtract time in Excel? Well, remember the 1904mode in Excel? It’s the magic wand in subtracting time!

I’ll leave the formula as is and just switch Excel to the 1904 Date system and Voila

Time in Excel

But what if we were subtracting 14:43 – 66:44? Well you’ll still need a 1904 date system but it will give you  -4:01. Now all we need is…

You guessed it [h]:mm Custom Format and you get -52:01.

Eternal happiness 🙂

Comments 4

  1. Mariza says:

    Here is my very complicated string for adding time — =IF((OR(E194=””,D194=””)),0,IF((E19440),((P194-40)),0))/2),0.25) which I use to calculate the end of the week -phew.

    your way seems to be simpler … wonder if I will try it

  2. Mariza says:

    Seem to have lost half my comment … here it is again.

    Here is my very complicated string for adding time —
    =IF((OR(E194=””,D194=””)),0,IF((E19440),((P194-40)),0))/2),0.25) – phew.

    This way seems to be simpler … wonder if I will try it

  3. I know this if off topic but I’m looking into starting my own webloig aand was wondering what all is required to get
    set up? I’m assuming having a blog like yours would cost a pretty
    penny? I’m not very internet savvy so I’m noot 100% positive.
    Any tips or advice would be greatly appreciated.
    Kudos

    1. Hi,
      it all depends on what you wish your blog to do. But here is a rundown and approximate costs…

      Getting a simple MyBlogsName.wordpress.com domain FREE
      Having a simple THEME that you cannot edit in almost any way… FREE

      Having your own domain (like excelunplugged.com) … about 7$ a year can be much more expensive (depends on the domain name!)
      Buying a theme that you can edit (edit visually, but still far from what you want if you want to make money with your blog)… about 20$ a year

      Having your domain and hosting … about 30$ a year (can be cheaper and also much more expensive)
      Being able to do whatever you want with your blog (adds, comprehensive statistics, being able to upload any kind of file and so on) comes free with WordPress.org on your hired server where you host your page…

      You really should think about what you need first and go for that initially since all migrations can be painful!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.