Decimal Time in Excel

Desert Andy 19:03 16 Mar 07
Locked

Is there an easy way to convert hours and minutes into decimal minutes in Excel; so that say 1hr 15min = 1.3

hzhzhzhz 20:25 16 Mar 07

Simsy 21:36 16 Mar 07

1 hour 15 mins = 1.25

Or do you actually want to round it to a single decimal place?

Regards,

Simsy

silverous 21:37 16 Mar 07

Do you really want 1hr 15min = 1.3 ? Surely it is 1.25 ?

The link given by hzhzhzhz looks like it will work as long as you don't have text e.g. "hr" and "min" in your cell.

If you need something that can handle times with the text in let me know.

Simsy 21:46 16 Mar 07

basically format the cell in question as a number, with the decimal places you want, and multiply the original cell by 24...

For example;

Cell A1 is formatted as [hh]:mm and contains 1:15

Cell B1 is formatted as number and contains =A1*24

this will give 1.25, if formatted to 2 places of decimals, 1.3 if formatted to a single decimal place.

Beware that if you have it formatted to show a single place, and you are using the result in any calculation, the figure used for calculation will not be the rounded up figure... it will be the 1.25...

Unless you have the "Precision as Displayed" option ticked. This is in Tools>Options>Calculation Note that this setting is for Excel as a whole, not just the spreadsheet you may be working on at the time. For this reason I don't think it's good to have it ticked.

If you do want to have a single decimal, and you need to make calculations based on it, it's better to round the figure in the calculation in the cell... eg

=Round(A1*24,1)

This rounds the calculation to 1 decimal place and the resulting figure would be used in any subsequent calculations.

Hope this helps,

Regards,

Simsy

Simsy 21:48 16 Mar 07

my "whatever" was referring to my original post, not yours silverous!

I realise that with your post, (which I hadn't seen when I started writing my second), between my two, it makes mine seem rather rude and abrupt.

rest assured that wasn't what was meant!

Regards,

Simsy

VoG II 22:08 16 Mar 07

How is the original data formatted?

silverous 23:46 16 Mar 07

Simsy - no prob :)

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac