# Off Topic > Tips and Tutorials >  >  Calculating Hours Worked

## lostanfoundjo

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

----------


## Chris

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?

----------


## dplooyem@telkom.co.za

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?

----------


## hvg118

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

----------


## fzyrdhatitude

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

----------


## DENISE

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

----------


## Lee Ann

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

----------


## ExcelTip

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)

----------


## 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 ?

----------


## Alan

Hi 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.

----------


## darwin51

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")

----------


## Alan

Hi darwin51,





> 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
> ...



I may be missing the point here, but why not just do something like this:

=-(Start1-Stop1)-(Start2-Stop2)-.....

Obviously you could put that in as an array formula to make it easier if you have more two or three starts and stops.

HTH,

Alan.

----------


## boomer

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

----------


## Alan

Hi Peter,





> 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



To convert a timevalue of 8:15 to the value 8.25 you just multiply by 24.

However, you can just sum up the timevalues as they are, and by keeping them as timevalues, they are easier to work with generally.

The other advantage is that you can format them to show up nicely as hours:mins or whatever you need.

HTH,

Alan.

----------


## tppcsjv

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.

----------


## Alan

Hi tppcsjv,





> 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.



That is correct - although especially if you want to enter times that go across midnight (which you do from what you say below).





> 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.



Time worked will simply be TimeOut-TimeIn.  Other calcs, such as over time etc will obviously depend on your terms, but might perhaps only kick in where someone has worked more than 50 hours a week (say)?





> 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.



That shouldn't matter if you enter the date / time in all cases.  It will still be TimeOut - TimeIn.  For example:

TimeIn = 23:00 hrs on 11 Nov 2005 (= 38667.958333... as a date / time value)

TimeOut = 07:00 hrs on 12 Nov 2005 (= 38668.291666... as a date / time value)

TimeOut - TimeIn = 8 hours (= 0.333... as a date / time value being 1/3 of a day)


HTH,

Alan.

----------


## january

How do l put times into formula and add up the hours and time be the pay rate

----------


## rodyzamora

IN	                      OUT	         IN	                     OUT                   REG HRS
4/14/06 6:00 PM	4/14/06 10:00 PM	4/14/06 11:00 PM	4/15/06 3:00 AM	        8


REG HOURS FORMULA =SUM((B2-A2)*24,(D2-C2)*24) RIGHT CLICK FORMAT NUMBER










> 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

----------


## rodyzamora

> 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.



     IN	      OUT	                      IN                   OUT         REG HRS
4/14/06 6:00 PM	4/14/06 10:00 PM	4/14/06 11:00 PM	4/15/06 3:00 AM	    8





FORMULA =SUM((B2-A2)*24,(D2-C2)*24) THEN RIGHT CLICK FORMAT TO NUMBER

----------


## jwcav

What if they only have the start time and stop time and the time for lunch

ie. 
Time In     Time Out    Lunch
7:30 AM    5:00 PM     :30

How would you calculate hours worked minus Lunch?  The result I am looking for is 9:00

Thanks

----------


## daddylonglegs

Your lunch would have to be a recognisable time in excel, probably 0:30 then you just use

=B2-A2-C2

format result cell as h:mm

where A2 contains start time, B2 end time and C2 lunch

If your shift might cross midnight, e.g. 11:00 PM to 07:00 AM change to

=MOD(B2-A2,1)-C2

----------


## erwan

Please anyone could you guys help me on how to Convert decimal value to time value, tq

----------


## oldchippy

Try this,

=A1/(60*24) then format to Time

----------


## daddylonglegs

> Please anyone could you guys help me on how to Convert decimal value to time value, tq



If you mean a decimal value in hours, e.g. to convert 8.6 to 8:36 just divide by 24, e.g.

=A1/24

format as [h]:mm

----------


## erwan

ok let say i have 150 minutes, how can i convert it to Hours and minutes format to be 2 hours and 30 minutes format and not 2.5 if i devide it to 60

----------

