What I thought was a simple Time format problem

[clanny]

I have about 2800 lines of data that haave a time stamp attached in the format

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num="0.68048611111111112">16:19:54</TD></TR></TBODY></TABLE>
The cells are formatted as "custom" and hh:mm:ss. Unfortunately, I need the hours and minutes in separate columns for calculations. I am trying to set up a calculation to show the solar angles at different times during the day and at any day of the year so need a column of hours and a column of minutes.

As you can understand I can't change 2800 lines of data one by one.

Any help would be much appreciated.

 

[VoG]

Hello and welcome to MrExcel.

Does this help? You can fill down the formulas.

Data Format Sheet

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="text-align: right;">16:19:54</td><td style="text-align: right;">16</td><td style="text-align: right;">19</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>=HOUR(A1)</td></tr><tr><td>C1</td><td>=MINUTE(A1)</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

 

[clanny]

Top work VoG - that works perfectly. Saved me an age there.

I'm trying to model a solar panel system for my honours project so it's quite likely I'll be on here a lot.

All the best.