Excel Time Formulas

littledave

littledave

littledave

Associate
Joined
25 Dec 2002
Posts
626
Location
Brighton
Just trying to create a timesheet to make my life easier each week.

Bit stuck on working out a formula to work out the total hours worked less lunch break.

Tried several different methods but none that seem to output as hours:minutes or when it does the value is incorrect.

formula.png
 
=SUM(B1-A1)-C1

B1 being Finish, A1 being Start, C1 being Lunch.

Replace with correct cell references & format cells to Time format.
 
Last edited:
Depending on formatting Is it not =(FinishTime - StartTime - LunchPeriod) ?

Seems to work ok for me...

Edit: Beaten...
 
ejr22 said:
=SUM(B1-A1)-C1

B1 being Finish, A1 being Start, C1 being Lunch.

Replace with correct cell references & format cells to Time format.
Click to expand...

Excellent thankyou but I still cant get it how I want

How do I set cell I8 to display a decimal ie 8.5 hours.

Capture1-1.png
 
Last edited:
Format as number, and use something like the following (you'll have to do you're calculation first), then apply this to the result:

=HOUR(A1)+MINUTE(A1)/60
 
You must log in or register to reply here.
Back
Top Bottom