Decimal Time in Excel

  Desert Andy 19:03 16 Mar 2007
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 2007

does this help? click here

  Simsy 21:36 16 Mar 2007

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 2007

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 2007

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 2007

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 2007

How is the original data formatted?

  silverous 23:46 16 Mar 2007

Simsy - no prob :)

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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?