Calculations in Excel

  debsym 14:43 11 Jul 2008


Does any one know how to do hours and minutes calculations in excel. I have to work out the take up of available time.
For example we have a library open for 9 hours 30 mins a day, it has 8 public access PCs so I need 8 X 9:30. Then I have to work out how much time was used and what that is as a percentage of the total time availabe. I have to do this for 26 libraries and then get a grand total at the end so I can put the figures in to one box as a percentage

  VoG II 14:56 11 Jul 2008

You can do calculations with times in Excel as if they were numbers (they are stored internally as numbers, 1 = 1 day). So with 09:30 in A1


will give you 8 x 09:30.

The problem is that the result will show as 04:00 because Excel displays a Time and not a Duration by only displaying the decimal part of the number. You can get around this by setting a Custom format for the result cell of [h]:mm and the correct result (76:00) will be displayed.

  debsym 15:10 11 Jul 2008

Thanks I have tried that and it will get me so far, I still have to go on and work it all out over a year. The first calculation looked to work but the next one I end up with value in the cell.

  VoG II 15:14 11 Jul 2008

In that case you may be better off converting your times to decimal hours.


with the result cell formatted as Number will give 9.50 for example. You can then do calculations using those decimal numbers.

  debsym 17:30 11 Jul 2008

Thanks, I will give that a go later and get back to you on it.

  nosharpe 18:25 11 Jul 2008

Following on what VoG said, you can use fractions to calculate hours in the day.
Eg. 1 hour = 1/24 or 9.5 hours = 19/48 or 9.5/24

Right click on a cell and select format cells.
Then choose fraction under the category, and select up to 2 digits.

You can also format the date & time in each cell in a similar way

  daba 01:13 12 Jul 2008

I've just tried VoGs cell format, and the calculated cell will only display total hours:minutes up to approximately 337 pc-years worth of hours.

However, I have continued my excel test sheet upto and beyond 1 million YEARS worth of hours, and although I can't display the hours correctly, all ratiometric calculations based on the cell data is valid. so 50% usage still displays correctly, even though the hours:minutes display is baulked.

You could try just ignoring the minutes (round the hours:minutes) to the nearest hour and display only hours.

Although, since you only need a range of 8 pc-years, then I suspect you have formula errors elsewhere, I don't think it is going to be that the accumulated hours is too large for Excel.

  debsym 09:30 12 Jul 2008

Thanks for your help. The excel spreadsheet is finished and I have an answer that looks good to me.

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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

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

Black Friday 2017 : date, sites participants & bonnes affaires