Excel working time calculation

Question

I am trying to put together an excel spreadsheet to track working time. A standard working day is say 8 hours with one hour lunch, say 40 hours total, 35 hours working. I need to track over and under time on a given day, and total this for a week period. The reason is to that if someone only takes a 30 minute lunch for three days, they can then take 1.5 hours off early at the end of the week for example.

Example daily record:

Mon - 9-6, 1 hour lunch = 8 working hours, 1 hour lunch, balance = 0 (the perfect day)
Tue - 8-6, 1 hour lunch = 9 working hours, 1 hour lunch, balance = +60 (one hour over)
Wed - 9-4, 1 hour lunch = 7 working hours, 1 hour lunch, balance = -60 (one hour under)
Thu - 9-6, 30 min lunch = 8 working hours, 30 min lunch, balance = +30 (30 mins over)
Fri - 9-6, 30 min lunch = 8 working hours, 30 min lunch, balance = +30 (30 mins over)

Example end of week totals

I am recording start/end/lunch times in excel in custom "time format" cells: 09:00 - 17:00 I am getting the hours with this formula: =TEXT(End-Start,"hh:mm") I enter the lunch time in "time format" 00:30 (30 mins) I get the actual time worked with this formula: =TEXT(Total-Lunch,"hh:mm")

When I try to add together / sum the values however I am not getting anything...

Any thoughts?

Please can you provide some sample data showing how you have the dates, start time, lunch, end time etc. set out. Also, this question may be better suited to the SuperUser site unless you are specifically after a programming solution (i.e. VBA). — ChrisProsser
– ChrisProsser, Commented Sep 26, 2013 at 12:41
Don't use TEXT function - as the name implies it returns a text result and when you sum text you get zero - just use =End-Start and use cell formatting to display in required format, same for =Total-Lunch — barry houdini
– barry houdini, Commented Sep 26, 2013 at 15:32

rajah9 · Accepted Answer · 2013-09-26 12:48:06Z

1

I do this with six columns

Date  / Start   / Lunch    / Lunch   / Stop    / total
3-sep / 8:29 AM / 12:30 PM / 1:30 PM / 5:44 PM / 8.25

For total, I calculate with the function =MROUND((D5-C5+F5-E5)*24,0.25). This rounds the total to the closest 0.25 hour (that is, 15 min).

Just for kicks, I accumulate the excess hours. Excel will put in the current time by pressing Ctrl+Shift ; (semicolon).

answered Sep 26, 2013 at 12:48

rajah9

12.5k5 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel working time calculation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related