Search code examples
excelexcel-2013

Converting large time format to decimal in excel

I'm trying to convert a large time value in excel to a decimal number for hours.

I currently have a column adding up "Ready time" for a call centre which is 3545:20:02 as a SUM. I now want that to show me the same hours in a decimal format e.g. 3545.333 as it's used in another calculation.

For reference, when I convert the above time to a General excel value, it is 147.7222454.

The formula I've been using is: =IFERROR((DAY(M54)*24) + HOUR(M54) + (MINUTE(M54)/60),0) and has been working fine for smaller time values.

Thanks in advance!

Solution

  • Excel counts in days (1 day = 1) so for hours you just multiply by 24, i.e.

    =M54*24

    format result cell as number with required number of decimals

    [the reason your current formula fails is because of DAY function - DAY is day of the month so it fails for you when the time value is >= 32*24 = 768 hours]