If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel Time Sheet - Rounding To Quarter Hours
I've got a daily time-tracking spreadsheet. The formula in A4 is:
=(A2A1)+A2-A1+(A3B2)+A3-B2 (A1 = Start Time, A2 = Lunch Begin, B2 = Lunch End, A3 = End Time, A4 = Total Hours Worked) Formatted as "[h]:mm" The formula works great, but I need the total hours to be rounded to the nearest quarter hour (.00, .25, .50, .75) Right now, if the total is 7 and a half hours, it comes out as "7.30" I've sent this question in previously and I believe the answer was to multiply the total by a number (can't remember what it was), but I needed to know how to fit that into the formula above properly and don't remember getting a response from that question. Hope that isn't too confusing. |
#2
|
|||
|
|||
Excel Time Sheet - Rounding To Quarter Hours
Multiply with 24
(your_formula)*24 to round to the nearest .25 (15 minutes in decimal form) =ROUND(((your_formula)*24)/0.25,0)*0.25 format as general, with 10:21 as result it returns 10.25 -- Regards, Peo Sjoblom "C A" wrote in message ... I've got a daily time-tracking spreadsheet. The formula in A4 is: =(A2A1)+A2-A1+(A3B2)+A3-B2 (A1 = Start Time, A2 = Lunch Begin, B2 = Lunch End, A3 = End Time, A4 = Total Hours Worked) Formatted as "[h]:mm" The formula works great, but I need the total hours to be rounded to the nearest quarter hour (.00, .25, .50, .75) Right now, if the total is 7 and a half hours, it comes out as "7.30" I've sent this question in previously and I believe the answer was to multiply the total by a number (can't remember what it was), but I needed to know how to fit that into the formula above properly and don't remember getting a response from that question. Hope that isn't too confusing. |
#3
|
|||
|
|||
Excel Time Sheet - Rounding To Quarter Hours
EXCELLENT. Thank you!
"Peo Sjoblom" wrote: Multiply with 24 (your_formula)*24 to round to the nearest .25 (15 minutes in decimal form) =ROUND(((your_formula)*24)/0.25,0)*0.25 format as general, with 10:21 as result it returns 10.25 -- Regards, Peo Sjoblom "C A" wrote in message ... I've got a daily time-tracking spreadsheet. The formula in A4 is: =(A2A1)+A2-A1+(A3B2)+A3-B2 (A1 = Start Time, A2 = Lunch Begin, B2 = Lunch End, A3 = End Time, A4 = Total Hours Worked) Formatted as "[h]:mm" The formula works great, but I need the total hours to be rounded to the nearest quarter hour (.00, .25, .50, .75) Right now, if the total is 7 and a half hours, it comes out as "7.30" I've sent this question in previously and I believe the answer was to multiply the total by a number (can't remember what it was), but I needed to know how to fit that into the formula above properly and don't remember getting a response from that question. Hope that isn't too confusing. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
HOW DO I CALCULATE TIME IN A TIME SHEET FOR EXCEL | RAFAEL | New Users | 1 | June 26th, 2005 11:32 PM |
Does excel recognise names rather than cells? | Sue | Worksheet Functions | 9 | May 22nd, 2005 04:51 AM |
I need an Excel bi-weekly employee time sheet template | Books | General Discussion | 4 | April 4th, 2005 12:43 AM |
time card excel sheet | Nancy Comello | Worksheet Functions | 1 | March 23rd, 2005 09:09 PM |