formula/version 2007
help with time sheet formula
time in/time out/time in/time out/time in/time out can use as much as 3 ins and outs per day 8:30 am to 12:30 pm and 1:30 pm to 4:30 pm, this should equal 7 hours worked; what if a person enters 8:25 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearest quarter hour this still should be 7 hours; or 8:22 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearst quarter hour this should be 7.25, not (15min.) basicly it needs to round both ways to nearest quarter hour no matter what time they enter. |
formula/version 2007
The basic formula would be:
=ROUND((end-start)*24/0.25,0)*0.25 If there are multiple start/end times: =ROUND(SUM(end1-start1,end2-start2)*24/0.25,0)*0.25 Format as General or Number -- Biff Microsoft Excel MVP "JackyP" wrote in message ... help with time sheet formula time in/time out/time in/time out/time in/time out can use as much as 3 ins and outs per day 8:30 am to 12:30 pm and 1:30 pm to 4:30 pm, this should equal 7 hours worked; what if a person enters 8:25 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearest quarter hour this still should be 7 hours; or 8:22 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearst quarter hour this should be 7.25, not (15min.) basicly it needs to round both ways to nearest quarter hour no matter what time they enter. |
formula/version 2007
Hi there.
The formula below rounds the difference between B2 (out) and A2 (in) to the nearest quarter. I guess you can adapt it to your demand: =IF(OR(ISBLANK(B2),ISBLANK(A2)),0,TIME(TRUNC((B2-A2)*24,0),VLOOKUP(((B2-A2)*24-D2)*60,{0,0;7.5,15;15,15;22.5,30;30,30;37.5,45;45, 45;52.5,60;60,60},2,TRUE),0)) Regards, Otávio "JackyP" wrote: help with time sheet formula time in/time out/time in/time out/time in/time out can use as much as 3 ins and outs per day 8:30 am to 12:30 pm and 1:30 pm to 4:30 pm, this should equal 7 hours worked; what if a person enters 8:25 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearest quarter hour this still should be 7 hours; or 8:22 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearst quarter hour this should be 7.25, not (15min.) basicly it needs to round both ways to nearest quarter hour no matter what time they enter. |
All times are GMT +1. The time now is 12:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com