Converting 1-60 minutes to quarter minutes in a formula
 

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.

Converting 1-60 minutes to quarter minutes in a formula
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lori" wrote in message
...
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.

Converting 1-60 minutes to quarter minutes in a formula
 

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.


In article ,
Lori wrote:

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.

Converting 1-60 minutes to quarter minutes in a formula
 

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


In article ,
"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lori" wrote in message
...
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.

Converting 1-60 minutes to quarter minutes in a formula
 

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
"JE McGimpsey" wrote:

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.


In article ,
Lori wrote:

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.

Converting 1-60 minutes to quarter minutes in a formula
 

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)



"Lori" wrote in message
...
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
"JE McGimpsey" wrote:

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.


In article ,
Lori wrote:

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.