[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Time calculation in excel 2010

Posted on 2012-08-12
5
Medium Priority
?
1,222 Views
Last Modified: 2013-06-28
Hi,
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.
Regards,
Geoff
0
Comment
Question by:gjjnc
5 Comments
 
LVL 81

Expert Comment

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

=(B1-A1)*24

Kevin
0
 

Author Comment

by:gjjnc
ID: 38286490
Hi,

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

regards,
Geoff
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38286505
Are your values 'time' only?  If so, to cater for change of day (and assuming it is only 1 day):

=IF(A1>A2,(A2+1)-A1,A2-A1)*24
0
 

Author Closing Comment

by:gjjnc
ID: 38286539
Thanks, that works fine.
0
 

Expert Comment

by:LoveCats
ID: 39285988
This formula takes into consideration the cross over of midnight.

=IF((OR(D5="",C5="")),0,IF((D5<C5),((D5-C5)*24)+24,(D5-C5)*24))

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

834 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