Excel Time Sheet - Rounding To Quarter Hours

[Guest]

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

 

[Guest]

EXCELLENT. Thank you!

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

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.