4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]

 Excel 2010/2013 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

 NEW Release: Access Advanced Level 4 and Level 5   dismiss
ERROR #3704: Operation is not allowed when the object is closed.

Calculating Differences in Dates & Times with Excel

 I recently re-wrote this Q&A so I could submit it to be published without screen captures. The original Q&A is below, with the images intact. The re-write clarifies some of the concepts, and is worth a read. Enjoy. There are several different techniques for calculating the difference between two dates or two times in Microsoft Excel. There are several functions you can use to accomplish this task, however this simple lesser-known technique works equally well in most situations, and is easier to use. In Excel, dates are stored as a number. Technically, the value of a date is stored internally as the number of days since 12/31/1899. To test this, just enter the number 1 in any cell, then right-click on that cell and select Format Cells. Change the format to Date and click OK. Now you'll see the date 1/1/1900. That's one day since 12/31/1899. Conveniently, time values are stored internally as fractions of a day. So if you want to store the time 12:00 AM, that's simply a 0. If you want 12:00 PM, that's given a value of 0.5, or half a day. The other time values are calculated accordingly. 6:00 PM is equal to 0.75. Knowing this, you can easily calculate the difference in days between two dates by simply subtracting them and making sure that number is formatted as a date. For example, in cell A1, enter the date 3/1/2012. In cell A2, enter the date 3/4/2012. Now in cell A3 you can subtract them: =A2-A1 The resulting value should be 3. There are three days between those two dates. Excel automatically formats this as a number for you. Calculating time values, however, gets a little trickier. Put the time value 5:00 PM in cell B1. Put 6:00 PM in cell B2. Now subtract them: =B2-B1 Excel returns the value of 1:00 AM as the result, which is not quite what you want. That's OK. Just format this cell as a Number type (again, right-click and select Format Cells, choose Number, click OK). Now you see the value is 0.04. Remember, however, that Excel treats time and date values as whole days. So what you have now is 0.04 days. Let's multiply that value by 24 to convert it to hours. Change cell B2 to read: =(B2-B1)*24 And now you can see the result is 1.0, which is what we expect. There is a 1 hour difference between 5 PM and 6 PM. This technique also works if you have a value that is a mixed date/time. For example, if you change B1 to "3/1/2012 6:00 PM" and change B2 to "3/2/2012 7:00 PM" you will see that there are 25 hours between those two times. Once you get used to it, you'll find that this is a much easier method for calculating the differences between dates and times than using all of the various functions that Excel provides. Q: I'm using Microsoft Excel. How can I take two columns of times and calculate the total difference in time between them. For example, column A is the time an employee clocked in and column B is the time the clocked out. I want to know how many hours each employee worked. A: This is simply a matter of subtracting the two times, and then formatting the cell as a number. Take this sheet for example: If you want to put the total hours worked in column C, all you have to do is subtract A from B: But now you're left with a time. That's because Excel assumes you're still dealing with times. Now, just right-click on the cell and select Format Cells. Change the format of this cell to a NUMBER. Now you get 0.08 which is fine... keeping in mind that Excel is thinking in DAYS, not hours. Just multiply this result by 24, and you have your hours worked. Autofill down the column... And you can see all of the hours worked. Of course, now you have a problem for your overnight workers. If they start at 11pm and finish at 7am, this won't work. But that will be the topic of a different Tip, or watch my full class on Excel Dates & Times: I cover dates and time in much more detail in my Excel 232 tutorial.

By Richard Rost

 You may want to read these articles from the 599CD News:
 5/4/2014 Microsoft Access Expert 20 3/5/2013 Excel Tip: Price Markups 2/3/2013 Signed Copies of my Excel Book 9/27/2012 Excel Expert 10 Handbook Ready 9/18/2012 Two New Excel Tips: Date/Time Differences 6/20/2012 Excel Tip: Sum Cells Between Two Values 6/16/2012 New Excel Tip: Paste Link 3/26/2012 Excel Tip: 2D Matrix Lookups 11/18/2011 Dashboards in Excel 10/6/2011 Excel Expert 11 On Hold

Learn

 Access index Excel index Word index Windows index PowerPoint index Photoshop index Visual Basic index ASP index Seminars More...
Customers

Online Theater
Insider Circle
Student Databases
Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order

Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us