Date and time functions

Hi,

I have a spreadsheet which has hidden data within the time and date fields which cause the sheet to not sort correctly, normally I need to go down and F2 in every field. The data layout is for a phone bill:

Called Number, Date, Time, Duration

I have a bit of code that runs through all of the rows for the Date which is already in the layout dd/mm/yyyyy and copies it to another cell using the dateValue function sort of in the format CellD2 = DateValue.CellA2. This seems to work as it copies the data over correctly but I cant find the code to do the same for the time fields (Time & Duration) Both of these fields are custom fields in the format hh:mm:ss. Is there a function that would let me copy one cell to another cell just in the above format? I have tried TimeValue but it doesn't like the original values.

Thanks

Hello Banco,

You can set the destination cell format by using the Format statement in VBA. In the example below, A1 has a regular time value. Cell B1 will convert this to the custom format "hh:mm:ss".

Range("B1").Value = Format(Range("A1"), "hh:mm:ss")

Sincerely,
Leith Ross

Thanks Leith,
I think I've managed to sort it by creating integers and setting them using the Hour Minute & Second functions and the concatenating them into another cell, I get different sheets from different companies and I think your solution will be great.

Many thanks,

banco