Excel Time Sheet - Rounding To Quarter Hours

[C A]

I've got a daily time-tracking spreadsheet. The formula in A4 is:

=(A2<A1)+A2-A1+(A3<B2)+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.

[Peo Sjoblom]

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" <[email protected]> wrote in message
news:[email protected]...
> I've got a daily time-tracking spreadsheet. The formula in A4 is:
>
> =(A2<A1)+A2-A1+(A3<B2)+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.

[C A]

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" <[email protected]> wrote in message
> news:[email protected]...
> > I've got a daily time-tracking spreadsheet. The formula in A4 is:
> >
> > =(A2<A1)+A2-A1+(A3<B2)+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.
>
>
>

[shanny24]

Hello,

I'm brand new to the forum and I am looking to get help cacluating hours worked by 40+ employees. I would like to calculate hours worked minus lunch in minutes and then being able to round to the quarter.
With our time cards if you work 8 minutes that rounds to 15 minutes. So I would be looking to have it calculate to 8.25 (8 hours and 15 minutes) 8.5 (8 hours and 30 minutes) and 8.75 (8 hours and 45 minutes).


I've been looking at the different formulas to calculate the hours worked. The report I run brings me lunch times in minutes. Is there a way to have a formula to include the total lunch minutes rather than the lunch start and stop time?

Thank you in advance!

[FDibbins]

Hello,

I'm brand new to the forum and I am looking to get help cacluating hours worked by 40+ employees. I would like to calculate hours worked minus lunch in minutes and then being able to round to the quarter.
With our time cards if you work 8 minutes that rounds to 15 minutes. So I would be looking to have it calculate to 8.25 (8 hours and 15 minutes) 8.5 (8 hours and 30 minutes) and 8.75 (8 hours and 45 minutes).


I've been looking at the different formulas to calculate the hours worked. The report I run brings me lunch times in minutes. Is there a way to have a formula to include the total lunch minutes rather than the lunch start and stop time?

Thank you in advance!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread