Date and time seperate time and calculate time duration

[stevembe]

Hope somebody can help and many thanks in advance for taking the time to read.

I have to create a spreadsheet that calculates time duration and there are three things I need to do. Firstly the data I get is a Date and Time in a single cell but what I need to do is separate the Date and Time into two different cells. After that I need to calculate the time elapsed between the two times as a manual example below shows:

<TABLE style="WIDTH: 569pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=758><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20 width=185>08/09/2011 19:15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=138>08/09/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=115>19:15:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>08/09/2011 19:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>08/09/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>19:20:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>00:05:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" colSpan=4>(Cell to left shows time elapsed)</TD></TR></TBODY></TABLE>

I have to have something together for tomorrow afternoon so any assistance before then would be very very much appreciated.

 

[Robert Mika]

For date:
=DATEVALUE(TEXT(A1,"DD/MM/YYYY"))

format as Date

For time:
=VALUE(RIGHT(TEXT(A1,"DD/MM/YYYY HH:MM"),5))

format as Time.
Then substraction is quite simple

 

[Toonies]

Hi try this

Excel Workbook
	A	B	C	D	E
1	08/09/2011 09:15		08/09/2011	09:15:00
2	08/09/2011 09:20	00:05	08/09/2011	09:20:00	00:05:00
Sheet

here is a sample file

http://www.mediafire.com/?fti51e8kvm17b6b

Toonies



<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=64></TD></TR></TABLE>

 

[stevembe]

Hi try this

Excel Workbook
	A	B	C	D	E
1	08/09/2011 09:15		08/09/2011	09:15:00
2	08/09/2011 09:20	00:05	08/09/2011	09:20:00	00:05:00
Sheet

here is a sample file

http://www.mediafire.com/?fti51e8kvm17b6b

Toonies



<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=64></TD></TR></TABLE>

Crikey Toonie, thats works an absolute dream, thank you so very much, thats is really appreciated

 

[barry houdini]

If you know that time and date in A2 is always later than that in A1 you don't need to separate them to get the time difference you only need to subtract one from the other, e.g. in A3

=A2-A1

format A3 as [h]:mm

If you need to separate them then you can get the date from A1 in B1 using this formula

=INT(A1)

and the time in C1 using this

=MOD(A1,1)

format B1 and C1 in required date/time formats as required

 

[Future]

For date:
=DATEVALUE(TEXT(A1,"DD/MM/YYYY"))

format as Date

For time:
=VALUE(RIGHT(TEXT(A1,"DD/MM/YYYY HH:MM"),5))

format as Time.
Then substraction is quite simple

Thanks a bunch for this solution.....