Is Excel's built in calendar/date/time system useful??

The above question came to me a few days ago while contemplating a users question on how to do something calendar related in Excel. It occurred to me to wonder -- we get a lot of questions on here that essentially seem to boil down to "how do I get Excel to work with dates and times". Personally, I have no need to use the built in calendar/date serials, and when I need to work with time, I prefer to convert my times to decimal hours, decimal minutes, or decimal seconds (depending on the time spans I'm working with) and perform my calculations that way. It has gotten me curious, though. How many of you find Excel's built in date/time system to be useful? How many find it to be too confusing to bother with? What makes it useful and what makes it confusing?

I think it's straightforward, easy to work with, and makes perfect sense.

I dont really have a problem using it, but when the question gets involved (working WD OT, WD DT, WE OT and WE DT) things can get messy - but not unmanageble

I think it would be a lot better of an excel date was treated as a unique integer like 1/1/1901 would be stored as m01d01y1901.

If you study how Excel stores dates, you will find that it does store dates as unique integers. http://www.cpearson.com/excel/datetime.htm Specifically, a date serial represents the number of days since 0 Jan, 1900. It may not be the exact same integer you had in mind, but it is a fairly well defined integer.

I can't just simply find the number of days, or months or years between two dates. I can't tell it Howmanyhrs(12/21/2014,12,22,2014).

As shg and FDibbins imply, it should be relatively easy to subtract two date/time serial numbers to get how many days (or hours or weeks) are in between those two date/time serial numbers. I think part of my question -- why is it so hard for some to use? Is it a lack of good documentation? Educational materials fail to explain it adequately? Is the concept of "decimal days since" a given starting date difficult for some to grasp?

Is that it -- the built in calendar just isn't used often enough for people to become comfortable or proficient in its use?

Originally Posted by JDI

I think the problem is that is isn't as intuitive as say subtracting two number.

I beg to differ. Dates are just integers to Excel. That's precisely what makes it useful. It's only not intuitive if you think of dates as a combination of the three elements of days months and years. Once you stop thinking in those terms (other than for formatting purposes) and mentally accept that a date IS a number it seems to me that it's extremely intuitive.

YMMV

I think part of my question -- why is it so hard for some to use? Is it a lack of good documentation?

From my experience, many users just simply do not understand the basic concept behind how excel displays (and works with) dates and times. For those reading this that can relate to that comment, here is an explanation I have used so often that I have a "canned reply" for it.....

You need to understand this about dates...a date is just a number indicating how many days have passed since 1/1/1900, so today 7/11/14 is actually 41831. excel formats it into something that we recognise as a date

Likewise with time...time is a decimal of 1 (day), so for instance 06:00 AM is actualy just 0.25, 12 noon is 0.5 and 06:00 PM (18:00) is 0.75. Again, excel formats it to something we see as a time

The flip-side to this mis-understanding, gets compounded when a user subtracts 2 dates (1/31/14-1/1/14) and, depending on the format where the answer will be, may see either 30 or 1/30/1900.
Likewise 1/1/14+30 could, again depending on the formula cell formatting, give 1/31/14 OR 41670...41670 is a date?????? what have you been smoking?? lol

An inexperienced user seeing 1/30/1900 or 41670 will quite naturally assume that either excel has bonkers or they have done something wrong.
Add into this mix, the fact that often, what looks like a date, is in fact, text that just looks like a date, and you end up with some totaly confuzzled users

Another mis-conception that I see is that just by formatting a "text" date, you can change it to something else. Again, some users do not understand that, for the most part, formatting is simply cosmetic - it changes how cell contents look (if it can), but does not change the actuall cell contents.
In my example above, changing cell contents from date to general, will change 1/31/14 to 41670, but a user that is not familiar with how dates work, will be left grasping at their sanity

We have the same (but maybe worse) situation with time.

06:00 AM + 1 should give you 07:00 Am, right? wrong, it seems to make no difference at all - what gives??? Sounds pretty logical, until you understand that 06:00 is in reality 0.25 to excel or 1/4 of a day. So adding 1 (a whole 1) to 06:00 (or 1/4 or 0.25), just added 1.00 to 0.25, sooooooo you still end up with 06:00 AM....but on the next day. Because teh cell is probably formatted as time, you wont see the extra day that was added, so it looks like excel cant add

And as if that wasnt confuddling enough, excel doesnt understand (or wont accept) negative time...unless you use a bit of trickery to get around that