Calculations in Excel

  debsym 14:43 11 Jul 08
Locked

Hi

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 08

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

=A1*8

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 08

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 08

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

=A1*24

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 08

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

  nosharpe 18:25 11 Jul 08

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 08

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 08

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.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5