Excel query re conversion of data

Delphi91

Hi all,

I have a spreadsheet (Excel 2003) with data in it in the form of decimal numbers (corresponding to a number of hours, e.g. 4.5 is 4 hours 30 mins.

I'm trying to get Excel to convert the 4.5 into something like 4:30, but I'm failing miserably. The formating of the cell to h:mm doesn't work. I've tried using the =TEXT function also, to no avail.

Any help would be appreciated.

seamus

Add in a column beside the one with the number of hours. Add in the following formula (assuming that the no. of hours is in Column

=B1/24

This will give you the number of days that the figure corresponds to. Then just apply a time format to your new column and you'll get the number of hours in a time format.

Delphi91

Tried that, no luck!

seamus

I updated my post, did you try that one? It works for me

Delphi91

Tried all sorts of formatting but failed.

OK, I've got A1 = 2360 (which is a number of minutes). In B1, I need the corresponding number of hours and minutes. At the moment, B1 contains =A1/60, which gives 38.33333.

I'd like it to show 38:20 in a format that I can then use to add successive entries in column B to give a total number of hours and minutes.

Delphi91

Ok, panic over, I managed to sort it.....

Capt'n Midnight

Excel counts time by days

hence you have to divide number of hours by 24 to get the fraction of a day