time difference

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
Basically I want to subtract time. The issue is with a time difference which goes on the the next day. Ex: 10/4/2006 23:30 - 10/5/2006 1:30. When I do this I use the formula, =A2+(A1-A2)-A1. This is my main question, I have the date in a different column and time in a different column. How do I concatenate both date and time to get to 10/4/2006 23:30 format. Then I can subtract times.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
If A1 contains the date, and B1 contains the time...

=A1+B1

...will return the date and time.

Hope this helps!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If A1 contains start date, B1 contains start time, A2 contains end date and B2 contains end time:

=(INT(A2)+B2)-(INT(A1)+B1)

formatted as [hh]:mm
 

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
That's great. Now, after doing the above I'm able to concatenate date and time into the date time format, i.e. the time format. Now, if I have a situation where the start time is 10/4/2006 23:30 and the end time is 10/5/2006 1:30. In the destination cell I'm using the formula, =(INT(A2)+B2)-(INT(A1)+B1).
 

Forum statistics

Threads
1,141,722
Messages
5,708,098
Members
421,546
Latest member
delatollas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top