» Calculating Hours Worked

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,

[QUOTE=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 ?[/QUOTE]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")

Hi darwin51,

[QUOTE=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")[/QUOTE]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.

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

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

Hi Peter,

[QUOTE=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[/QUOTE]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.

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

Hi tppcsjv,

[QUOTE=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.[/quote]That is correct - although especially if you want to enter times that go across midnight (which you do from what you say below).

[quote]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.[/quote]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)?

[quote]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.[/QUOTE]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.

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

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






[QUOTE=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[/QUOTE]

[QUOTE=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.[/QUOTE]

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

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

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

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

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

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

Try this,

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

[QUOTE=erwan]Please anyone could you guys help me on how to Convert decimal value to time value, tq[/QUOTE]

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

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