Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
Converting hours:minutes:seconds to just minutes | Excel Worksheet Functions | |||
converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
Converting Hours and Minutes to Just Minutes | Excel Discussion (Misc queries) | |||
Formula for minutes to days:hours:minutes | Excel Discussion (Misc queries) |