Converting 1-60 minutes to quarter minutes in a formula

[Guest]

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.

 

[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

 

[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.

 

[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().

 

[Guest]

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

 

[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)