Converting 1-60 minutes to quarter minutes in a formula

L

Lori

I am working on a timesheet that adds our time up, but I would like on the
total sum of each day to change to quarter time. Is that possible? For
example, an employee enters in at 8:30, out at 12:00, in at 12:30, out at
5:30, the total for that is 8 hours 30 minutes, it shows 08:30. I want to
change that total to read 8.5 instead of 30 minutes. Maybe a formula to
change 01 min thru 15 minutes to .25 and so on. Any help? Thanks.
 
B

Bernard Liengme

With 8:30 in A1, =A1*24 gives 8.5 when the cell is formatted general
Do you also want to round to nearest 1/4 hour (0.25?)
=MROUND(A1*24,0.25) or =ROUND(A1*24*4,0)/4
The MROUND function requires the Analysis Toolpac
best wishes
 
J

JE McGimpsey

One way:

Assuming 'In' in Columns A & C, 'Out' in columns B & D:

=CEILING((B1+D1-A1-C1)*24,0.25)

XL stores times as fractional days, so multiplying by 24 produces hours
as integers.
 
J

JE McGimpsey

Note that MROUND() or ROUND() will round 01-07 minutes to .00, not .25.
If you really want 01 minute to round to 0.25, use CEILING() or
ROUNDUP().
 
L

Lori

Hi, in an earlier reply you asked if I really wanted to round 1 min to .25.
No, I do not, didn't catch that one! So, does the CEILING make it round up
or down accordingly?
Thanks again, Lori
 
B

Bob Phillips

ceiling rounds up, you want the second formula Bernard gave you

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top