Time calculation in excel 2010

Posted on 2012-08-12
Last Modified: 2013-06-28
I need some help on a calculation in excel 2010. I want to have a formula work out the number of hours between two times that fall on two different days. E.g. 11pm to 7am. Any help would be appreciated.
Question by:gjjnc
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    Assuming A1 is the start date/time and B1 is the stop date/time:



    Author Comment


    When I enter the data as above i get a whole lot of ####### with a message "dates and times that are negative are to large to display. The cells are start time A1 and finish time A2

    LVL 25

    Accepted Solution

    Are your values 'time' only?  If so, to cater for change of day (and assuming it is only 1 day):


    Author Closing Comment

    Thanks, that works fine.

    Expert Comment

    This formula takes into consideration the cross over of midnight.


    Where D5 is the end time and C5 is the start time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now