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

x
Solved

# Time calculation in excel 2010

Posted on 2012-08-12
Medium Priority
1,222 Views
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
Question by:gjjnc

LVL 81

Expert Comment

ID: 38286433
Assuming A1 is the start date/time and B1 is the stop date/time:

=(B1-A1)*24

Kevin
0

Author Comment

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

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

ID: 38286539
Thanks, that works fine.
0

Expert Comment

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

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…
###### Suggested Courses
Course of the Month18 days, 22 hours left to enroll