HELP!! Time formula

[MarkAndrews]

Hi Guys,

I havent posted on here for many years

Im using Office 2010

I have a spreadsheet which i need to calculate the time between but i cannot for the life of me remember how to do this so i wonder if someone could kindly point me in the right direction

In Column B (EG B2) i have this data "2017-09-01 09:00:00 +0100" I need to extract the time only (09:00:00) to go in C2
In Coumn D (EG D2) I have this date "2017-09-01 11:00:00 +0100" i need to extract the time only (11:00:00) to go in E2

Then what i need to do is calculate the difference (EG E2-C2) to go in time in cell F2 and copy down the page

Any help greatly appreciated as this is driving me mad

Many thanks

 

[etaf]

i did show that in H2 as i mentioned if date need to be considred and how to format the cell
=LEFT(D2,20)*1 - LEFT(B2,20)*1
[H]:MM:SS

BUT if you want to include any of the dates if over 24hrs difference

then
=LEFT(D2,20)*1 - LEFT(B2,20)*1

 

[MarkAndrews]

i did show that in H2 as i mentioned if date need to be considred and how to format the cell
=LEFT(D2,20)*1 - LEFT(B2,20)*1
[H]:MM:SS

How would i get the result in C2 to show as "DD/MM/YYYY HH:MM" and still be able to do the original calculations as per original post

Apologies i always struggle with time and date calculations so any help greatly appreciated

 

[etaf]

in c2 put
=LEFT(B2,20)*1
in e2 put
=LEFT(D2,20)*1

format the cell as
DD/MM/YYYY HH:MM

Book6
	A	B	C	D	E	F	G	H
1
2		2017-09-01 09:00:00 +0100	9/1/17 9:00	2017-09-01 11:00:00 +0100	9/1/17 11:00	0.083333333		0.083333333
3
Sheet1

Cell Formulas
Range	Formula
C2,E2	C2	=LEFT(B2,20)*1
F2	F2	=TIMEVALUE(MID(D2,11,9)) - TIMEVALUE(MID(B2,11,9))
H2	H2	=LEFT(D2,20)*1 - LEFT(B2,20)*1

 

[MarkAndrews]

in c2 put
=LEFT(B2,20)*1
in e2 put
=LEFT(D2,20)*1

format the cell as
DD/MM/YYYY HH:MM

Book6
	A	B	C	D	E	F	G	H
1
2		2017-09-01 09:00:00 +0100	9/1/17 9:00	2017-09-01 11:00:00 +0100	9/1/17 11:00	0.083333333		0.083333333
3
Sheet1

Cell Formulas
Range	Formula
C2,E2	C2	=LEFT(B2,20)*1
F2	F2	=TIMEVALUE(MID(D2,11,9)) - TIMEVALUE(MID(B2,11,9))
H2	H2	=LEFT(D2,20)*1 - LEFT(B2,20)*1

Absolute legend! Thankyou so much!!

 

[etaf]

you are welcome

 

[Sufiyan97]

How would i extract not only the time but the date too as a valid excel date? as "DD/MM/YYYY"

Many thanks

Try

Book4
	A	B	C	D	E	F	G
1
2	Date and Time	2017-09-01 09:00:00 +0100	9/1/2017 9:00	2017-09-01 11:00:00 +0100	9/1/2017 11:00	2:00:00
3	Date Only	2017-09-01 09:00:00 +0100	09/01/2017	2017-09-01 11:00:00 +0100	09/01/2017	0:00:00
4	Time Only	2017-09-01 09:00:00 +0100	9:00:00	2017-09-01 11:00:00 +0100	11:00:00	2:00:00
5
6
Sheet1

Cell Formulas
Range	Formula
E2,C2	E2	=LEFT(D2,19)+0
F2:F4	F2	=E2-C2
E3,C3	E3	=LEFT(D3,11)+0
E4,C4	E4	=MID(D2,SEARCH("??:??:??",D2),8)+0