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

 

[Sufiyan97]

Try

Book12
	A	B	C	D	E	F	G
1
2		2017-09-01 09:00:00 +0100	9:00:00	2017-09-01 11:00:00 +0100	11:00:00	2:00:00
3
4
5
Sheet1

Cell Formulas
Range	Formula
C2,E2	C2	=MID(B2,SEARCH("??:??:??",B2),8)+0
F2	F2	=E2-C2

Custom format for column C & E - "[h]:mm:ss"

 

[etaf]

try
if you dont want to inclde any date difference

=TIMEVALUE(MID(D2,11,9)) - TIMEVALUE(MID(B2,11,9))

format cell as TIME

Book1
	A	B	C	D	E	F
1
2		2017-09-01 09:00:00 +0100		2017-09-01 11:00:00 +0100		2:00:00
Sheet1

Cell Formulas
Range	Formula
F2	F2	=TIMEVALUE(MID(D2,11,9)) - TIMEVALUE(MID(B2,11,9))

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

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

Book1
	A	B	C	D	E	F	G	H
1
2		2017-09-01 09:00:00 +0100		2017-09-01 11:00:00 +0100		2:00:00		2:00:00
Sheet1

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

format the cell as [H}:MM:SS
so that it shows over 24hrs

 

[Fluff]

Im using Office 2010

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MarkAndrews]

Try

Book12
	A	B	C	D	E	F	G
1
2		2017-09-01 09:00:00 +0100	9:00:00	2017-09-01 11:00:00 +0100	11:00:00	2:00:00
3
4
5
Sheet1

Cell Formulas
Range	Formula
C2,E2	C2	=MID(B2,SEARCH("??:??:??",B2),8)+0
F2	F2	=E2-C2

Custom format for column C & E - "[h]:mm:ss"

Perfect, thankyou so much

 

[MarkAndrews]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I will do, thanks for the advice

 

[MarkAndrews]

try
if you dont want to inclde any date difference

=TIMEVALUE(MID(D2,11,9)) - TIMEVALUE(MID(B2,11,9))

format cell as TIME

Book1
	A	B	C	D	E	F
1
2		2017-09-01 09:00:00 +0100		2017-09-01 11:00:00 +0100		2:00:00
Sheet1

Cell Formulas
Range	Formula
F2	F2	=TIMEVALUE(MID(D2,11,9)) - TIMEVALUE(MID(B2,11,9))

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

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

Book1
	A	B	C	D	E	F	G	H
1
2		2017-09-01 09:00:00 +0100		2017-09-01 11:00:00 +0100		2:00:00		2:00:00
Sheet1

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

format the cell as [H}:MM:SS
so that it shows over 24hrs

This also works perfect to, many thanks

 

[etaf]

you are welcome

 

[Sufiyan97]

You're welcome
Glad we could help!

 

[MarkAndrews]

You're welcome
Glad we could help!

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

Many thanks