Thread: Calculating Hours Worked

I can do the timesheet formula for adding the hours worked as follows:

Start Finish Total
08:45 17:15 8.5

However, it doesn't work when I fill in a whole week work of hours in this format:

Start Finish Total
08:45 17:15 08:30

It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??

Thnaks in advance, H

online classes, books, etc. that deals with intermediate and advanced excel formulas, and importing information via access, from SQL systems.

I need to track up to 20 employees daily hours worked.

The start time is 6pm the end time is sometimes after midnight. I also need tp deduct lunch breaks, to get total hours for each employee. I have the basics of excel and can get the formula to work for times before midnight but not after. Not sure how to format cells in time format and what to do after.

Can someone help me I am very frustrated and not that good with excel.

Lee Ann

I need to subtract one date stamp (yyyymmdd hhmmss) from each other to get to the total amount of time spent on each job. Is there a formula for this?

I have a very simple time sheet, I just need to calculate Regular hours, overtime and total, is there a way that regular hours will only add to 40. and the remaining hours go to overtime?

oh my god now i am really confused. I clock in... and out online. it just tells me the time in and time out. I am having such a hard time figuring out how many hours i work in a week i am not allowed to work more than 35 hours. can someone help

OK I HAVE A WORK SHEET DONE BY MY COMPANY ON EXCEL WHAT FORMULA CAN I USE TO ADD MY HOURS FOR ONE DAY TAKING OUT MY ONE HOUR LUNCH SO IT CAN SHOW UP ON EXCEL??
I START AT 8AM END 5:30 AND TAKE AN HOUR FROM LUNCH

Problem:

Columns A:C contain ID\'s, dates and times. Each row indicates the time a particular person signed in or out of work.
We want to determine which of the entries in column C are times signed in and which are times signed out, and then we want to calculate the number of hours worked by each person.

Solution:

To determine whether a time represents signing in or out, enter the following CHOOSE formula in column D:
{=CHOOSE(MAX((A2=$A$2:$A$9)*(ROW()
Thus, \"\"Time In\"\" or \"\"Time Out\"\" will be displayed next to each time shown in column C.

Then, to calculate the number of hours worked by each person, enter the following Array formula in column E:
{=SUM(IF((D2=\"\"Time Out\"\")*(A2=$A$2:$A$9)*($D$2:$D$9=\"\"Time In\"\"),C2-$C$2:$C$9,\"\"\"\"))}
Thus, the number of worked hours will be displayed next to the \"\"Time Out\"\" indicator matching each ID.

(To create Array formula: select the cell after typing the formula,press F2 and then press Ctrl+Shift+Enter)

I am not really good with some of these formulae, so tried it. The CHOOSE formula in the example is not working.
I entered this formula in in COLUMN D
(Column A = A numeric ID, Column B - Date, Column C contains the times - formatted as time)

Tried with 9 rows and more - is there a typo or am I doing something wrong ?

Hi samirl,

Originally Posted by samirl

I am not really good with some of these formulae, so tried it. The CHOOSE formula in the example is not working.
I entered this formula in in COLUMN D
(Column A = A numeric ID, Column B - Date, Column C contains the times - formatted as time)

Tried with 9 rows and more - is there a typo or am I doing something wrong ?

Sorry, but I cannot make out what you mean.

Could you post back with some sample data, the formulae you are using, the results you are getting, and the results you want to get.

Thanks,

Alan.

Thats a good tip, but what if you want to work out the total daily hours worked minus your lunch break ?

Try this


Day Date Start Start End Finish Hours Lunch Hours
Monday 01/08/2005 07:45 12:00 12:30 16:30 08:45 00:30 08:15 8.25
Tuesday 02/08/2005 07:45 12:00 12:30 16:30 08:45 00:30 08:15 8.25
Wednesday 03/08/2005 07:45 12:00 12:30 16:30 08:45 00:30 08:15 8.25
Thursday 04/08/2005 07:45 12:00 12:30 16:30 08:45 00:30 08:15 8.25
Friday 05/08/2005 07:45 00:00 00:00 12:15 04:30 00:00 04:30 4.5
Saturday 06/08/2005 00:00 00:00 00:00 00:00 00:00 00:00 00:00 0
Sunday 07/08/2005 00:00 00:00 00:00 00:00 00:00 00:00 00:00 0
Total 37.5



if you wanted to work out gross hours for monday, enter this in the same row as mondays hours (In cell H): =TEXT(G6-D6,"hh:mm")
Lunch Total Time would be (In Cell I): =TEXT(F6-E6,"hh:mm")

Nett hours minus lunch would be (IN Cell J): =TEXT(H6-I6,"hh:mm")

Your example works fine when I tried it however how did you
convert 08:15 to 8.25 so the weekly hours can be summed up?

Thanks

Peter

I am trying to figure out how to add up hours worked in the excel sheets. All of the previous forum admissions that I have read have talked about making sure to associate the formula with the date and year. All I want to do is put in a formula that, after I enter the time in and time out, will calculate for me the total hours worked and allocate time and a half and double time for me. We have a column for time in, another for time out, one for regular time, one for over time, one for double time, one for paid time off and each paid week has a total at the bottom. We also have staff that start work at 11pm one day and get off of work at 7am the next morning. Please help. Thanx in advance.