Utilisation percentage of hours

[Ratan Bhushan]

Hi frineds

I am making a sheet in which I want Utilisation percentage of working hours. The problem I am facing is the working hours I have in column@ "AG" has ":" and utilisation percentage is in "AI" column where I have applied the formula but due to ":" in "AG" column its not calculatiing correctly.
I have attached the file.
Please help me with this. I have highlighted the entire columns.

 

[Debraj]

Hi Ratan,

How about a more simpler function..

=(AG8*24)/AH8

PS : convert the Time format to Number.. you can see total Working hours are adding and showing total in days....

 

[Hui]

Ratan

the solution is a lot simpler than your expecting
AG8 is showing the result of 76:30 76 Hrs 30 mins, but internally Excel is storing that as Days.Decimal days
If you select AG8 and press the , icon it will show 3.19 (Days)

AH8 is already in Hrs

So in AI8 simply change that to =24*AG8/AH8

All fixed

 

[Ratan Bhushan]

Ratan

the solution is a lot simpler than your expecting
AG8 is showing the result of 76:30 76 Hrs 30 mins, but internally Excel is storing that as Days.Decimal days
If you select AG8 and press the , icon it will show 3.19 (Days)

AH8 is already in Hrs

So in AI8 simply change that to =24*AG8/AH8

All fixed

Sir, it is working, you are great, but I did not understand the logic behind this, why did we multiply this with 24? Please to make me understand sir?

Thanks a ton

 

[Hui]

AG8 has a number 3.19
Which is being displayed as 76:30 76 Hrs 30 mins
Internally Excel stores Dates as Integers and Times as Decimal parts of 1
In AG8 you have added up enough times to get 3.19 days

In AH8 the number is straight hours as it is 22 days x 9.5 Hrs per day

So to get the utilisation you need to convert it all to hours

Luckily we know there are 24 Hrs in a day and so simply multiplying AG8 x 24 will give us the number of Hours, which you then divide by AH8, to get the utilisation in %

 

[Ratan Bhushan]

AG8 has a number 3.19
Which is being displayed as 76:30 76 Hrs 30 mins
Internally Excel stores Dates as Integers and Times as Decimal parts of 1
In AG8 you have added up enough times to get 3.19 days

In AH8 the number is straight hours as it is 22 days x 9.5 Hrs per day

So to get the utilisation you need to convert it all to hours

Luckily we know there are 24 Hrs in a day and so simply multiplying AG8 x 24 will give us the number of Hours, which you then divide by AH8, to get the utilisation in %

You are awesome, superlike