Seperating Date and Time

[Rocket28]

Hi,

I have a d ata dowload form an external website showing date and times personel have worked. I can download this into excel, and i want to calcualte the total hours worked each day, and then hours worked if over 6pm.

Trouble is though, when it downloads, the date and time are in one cell.

<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=111><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=25 width=111>20/02/2011 10:02</TD></TR></TBODY></TABLE>

How do i break this apart? I try =left(e5,10) to et the date, and change the format odf the cell to date, but ic comes up with

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 18.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25 width=64>40593.6146</TD></TR></TBODY></TABLE>

Any ideas?

Thanks

 

[Andrew Poulsom]

Try eg:

=INT(A1)
=MOD(A1,1)

 

[VoG]

Try

Excel Workbook
	A	B	C
1	20/02/2011 10:02	20/02/2011	10:02
Sheet3

 

[Rocket28]

Brilliant. =INT(a1) allows me to see the date, but what about the time?

 

[Andrew Poulsom]

The time is:

=MOD(A1,1)

 

[Rocket28]

Mod for time. Cheers.

Ive tried this, and i get

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" span=3 width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 width=108 align=right>19/02/2011 14:45</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=108 align=right>19/02/2011 00:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=108 align=right>00/01/1900 14:45</TD></TR></TBODY></TABLE>

I can see it is picking up the right info, but the cells are still showing date and time in the same cell.

 

[VoG]

You need to format the date cell as Date and the time cell as Time.

 

[Rocket28]

Ah! Silly me. I thought i had these formated.

Thanks again! You have really helped me out.

 

[Neologism]

I always had problems with the =int and =mod functions so I've gotten used to playing chop shop like this:

=DATE(RIGHT(LEFT(E5,10),4),RIGHT(LEFT(E5,5),2),LEFT(E5,2))

As for the time:

=TIME(LEFT(RIGHT(E5,5),2),RIGHT(E5,2),)

Regards,
-=B=-