Converting minutes into percentages of hours

[clusher]

Hi,

I am creating a spreadsheet that is going to be used to calculate employee hours. In one column they will input their in time and in the other they will input their out time then in the next column it sums their total hours worked. In the in and out column they must put their exact time worked. However, i need another column where it will convert their total time worked into percentages of hours, i.e., :00, :25, :50, :75. for example, if they worked 1 hour and 30 minutes it is converted to 1.50 hours. the hours also have to be rounded up or down depending on the the minutes in the total time column, any minute that ends in a 7 or below is rounded down and any minute that ends in 8 or above is rounded up i.e., 1:07 is 1:00 and 1:08 is 1:15. if you can help me i would greaty appriciate it.

clusher

 

[PaddyD]

Hi - welcome to the board.

Some generic formulas:

Rounding to nearest quarter hour: =MROUND(A22,1/(24*4))
Rounding down to nearest quarter hour: =FLOOR(A22,1/(24*4))
Rounding Up to nearest quarter hour: =CEILING(A22,1/(24*4))
Rounding Up to nearest half hour: =CEILING(A22,1/(24*2))

...hope it helps. post back if not.

 

[Barry Katcher]

I'm a little confused. On one hand, you seem to be asking for your Total time (end minus start) to be in 100ths instead of 60ths - no problem. But you also seem to be asking for the total column to be rounded to nearest 15 minutes. I may have misread your message, but could you post an example of how your sheet is laid out and what results you want in which column?

 

[Ian Mac]

Hello,

Use MROUND()

Like:

Book1
	A	B	C	D
2	1:08	1.25
3	1:07	1
4	1:33	1.5
5	1:47	1.75
6	1:39	1.75
7	1:58	2
Sheet2

Hope this helps.

Note: MROUND() is part of the Analysis Toolpak, under Tools>Add-ins

 

[Ekim]

Clusher,

If you use Paddy's formula "=MROUND(A2,1/(24*4))", format your result cell as Time (h:mm). If you use Ian's formula "=MROUND(A2*24,0.25)" format your result cell as a Number (2 decimal places).

Regards,

Mike

 

[PaddyD]

Hi Ekim!

Just a small moment of pedantry on my part!

As the OP is doing this for a "total time worked" column, it's probably better to use the 'elapsed time' format: [hh]:mm

...& I should probably have pointed that out in my reply - thanks for the prompt

paddy