Need Formula to subtract Different date and time

**Chobi** · 01-09-2011, 12:12 AM

Hello all....

I am trying to find a formula to subtract 2 different dates and times, and come up with an hhhh:mm formated answer.

For example,

Cell No. A1 has 1/20/10 04:46
Cell No. A2 has 2/5/10 18:10

I can get the hours to come out right, but it doesn't include multiple days.

Thanks for your help.

**Chobi** · 01-09-2011, 12:18 AM

Let me try to clear this up a little bit more....

Cell A1 has 1/20/10
Cell A2 has 04:46
Cell A3 has 2/5/10
Cell A4 has 18:10

I know the formula for subtracting time is A4+(A2>A4)-A2

But is there any way to get the dates broken down to hours?

Thanks

**jeffreybrown** · 01-09-2011, 12:22 AM

=((A2+(A2<A1))-A1)

and then put a custom format of [h]:mm:ss

If you want to see the result as 16 days, 13.40 hours

try...

INT(A2-A1)&" days, "&TEXT(MOD(A2-A1,1)*24,"0.00")&" hours"

**Chobi** · 01-09-2011, 12:48 AM

Thanks for the quick reply!

**jeffreybrown** · 01-09-2011, 12:59 AM

If you are satisfied with the answer provided, please don’t forget to mark the thread as solved.

How to mark a thread Solved
Go to the first post
Select
-- Edit
-- Go Advanced
-- Below the word Title you will see a dropdown with the word No prefix
-- Update to Solved and then select Save

**DonkeyOte** · 01-09-2011, 05:17 AM

@Chobi, we have several Q & A forums - Tips & Tutorials is not one of them - please ensure you post in the most appropriate forum for your question.

Thread moved to Worksheet Functions.

**potterands** · 07-17-2013, 05:51 AM

I have a similar request

Cell A1 has 20/06/2013
Cell A2 has 14:05:00
Cell A3 has 21/06/2013
Cell A4 has 18:10:00

I need to get the combined difference in hours between the combined A3&A4 to A1&A2 but bring it back in hours & mins

Thanks
Andy

**arlu1201** · 07-17-2013, 06:03 AM

potterands,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.