jaytc2003

iTrader: (1)

Hi, I am doing a spreadsheet at work that pulls info in from external data sources.

One of the sources, has data contained in a time format and I need to break this down into the component form of the hour and also the minutes (2 seperate cells one for each).

So for an example

Cell c1 contains 12:30 formatted as time -this is data from the external source (Kronos)

I want cell a1 to contain 12 and b1 to contain 30

I have tried the left function but that doesnt work correctly, I was thinking about getting all of the times on a seperate worksheet and getting their decimals numbers and then using a vlookup against the decimal to populate the cells but that is a lot of work and possibly wont be easy to maintain in the future.

Has anyone got any suggestions?
Cheers
James

jaytc2003

iTrader: (1)

looks like I have sorted it by using

=DAY(T4)*24+HOUR(T4)+MINUTE(T4)/60

this puts the time in a cell then I can use left and right in the other cells to get the info I need