How do I get the difference in time between 2 cells in this type of date-time cells?

Hi,

I have a big data (thousands of rows) excel file where each row is assigned a time (as shown below).
I want to easily calculate the difference in time between 2 cells every other row (just as I filled in column C manually for the first 3 below).
So I want the difference in time between row 1&2 3&4 5&6 7&8 etc. etc.
The format of the date-time cells are:
Custom dd/mm/yyyy hh:mm

Any help would be greatly appreciated


A B C
1 2015.03.23 14:30
2 2015.03.23 18:30 04:00
3 2015.03.23 19:30
4 2015.03.23 20:17 00:47
5 2015.03.23 21:30
6 2015.03.24 01:00 03:30
7 2015.03.24 02:00
8 2015.03.24 03:00
9 2015.03.24 03:30
10 2015.03.24 08:00
11 2015.03.24 09:00
12 2015.03.24 10:00
13 2015.03.24 11:00
14 2015.03.24 14:30



edit: hmm when posted it does not seem to align the columns right, I hope you still understand.

Enter this formula in B2 and copy down as needed:

=IF(ISEVEN(ROW()),A2-A1,"")

You may have to replace the commas with semi-colons.

Welcome to the forum.

To convert the times in column A to Excel readable times enter this formula in B1 and fill down and format as date and time.

Formula:

Please Login or Register  to view this content.

To subtract times on every second row enter this in C2 and fill down formatted as [h]:mm

Formula:

Please Login or Register  to view this content.

	A	B	C
1	2015.03.23 14:30	23/03/2015 14:30
2	2015.03.23 18:30	23/03/2015 18:30	4:00
3	2015.03.23 19:30	23/03/2015 19:30
4	2015.03.23 20:17	23/03/2015 20:17	0:47
5	2015.03.23 21:30	23/03/2015 21:30
6	2015.03.24 01:00	24/03/2015 1:00	3:30
7	2015.03.24 02:00	24/03/2015 2:00
8	2015.03.24 03:00	24/03/2015 3:00	1:00
9	2015.03.24 03:30	24/03/2015 3:30
10	2015.03.24 08:0	24/03/2015 8:00	4:30
11	2015.03.24 09:0	24/03/2015 9:00
12	2015.03.24 10:0	24/03/2015 10:00	1:00
13	2015.03.24 11:0	24/03/2015 11:00
14	2015.03.24 14:3	24/03/2015 14:03	3:03

Paste into B2 and fill down

Formula:

Please Login or Register  to view this content.

Originally Posted by newdoverman

To convert the times in column A to Excel readable times enter this formula in B1 and fill down and format as date and time.
=--SUBSTITUTE(A1,".","/")

I'm assuming these are true date/times in a regional format (Netherlands).

Edit: I will post this in a different thread, so others may find it easier.




Thank you everybody for the amazing answers! I got this working now

Now I only have 1 step left:

I sorted a different part of the data from low to high (where in this case "-27.49" is the lowest and "16.14" is the highest) as part of it shown below.
Now I would like a histogram from the data where everything is sorted in bins of half an hour. so the first bin would be from 15-45 minutes, second bin 45-75 minutes etc. up to 14 hour (which is the maximum time for a value)

When I try picking a histogram from this raw data (transferred to a new sheet) I get a blanc graph.

Thanks in advance!

p.s. sorry for asking "dumb" questions, I am not used to this type of data handling, and especially not in excel 2016 (used to work with old versions)




-27.49 08:30:00
-21.6 15:00:00
-15.93 06:00:00
-14.98 09:30:00
2.29 01:00:00
2.29 02:30:00
2.31 02:00:00
2.31 00:30:00
2.31 02:30:00
2.32 01:30:00
2.32 00:30:00
2.33 01:00:00
2.33 01:00:00
6.79 01:00:00
7.11 01:30:00
7.31 00:30:00
10.35 09:30:00
12.77 01:00:00
13.15 02:00:00
16.14 01:30:00