CHOOSE Formula not working ?
samirl wrote on August 03, 2005 14:34 EST |
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 ? |
Reply: samirl
Alan wrote on August 04, 2005 02:07 EST |
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. |
Hours Worked
darwin51 wrote on August 04, 2005 05:26 EST |
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") |
Reply: darwin51
Alan wrote on August 05, 2005 00:41 EST |
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. |
looking for
fzyrdhatitude wrote on December 31, 1969 19:00 EST |
| online classes, books, etc. that deals with intermediate and advanced excel formulas, and importing information via access, from SQL systems. |
Calculating Hours Worked
boomer wrote on August 11, 2005 17:33 EST |
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 |
Reply: boomer
Alan wrote on August 12, 2005 00:28 EST |
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. |
Creating a timesheet with after midnight values
Lee Ann wrote on December 31, 1969 19:00 EST |
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 |
Help with Time Sheet
tppcsjv wrote on November 09, 2005 14:43 EST |
| 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. |
Reply: tppcsjv
Alan wrote on November 10, 2005 19:07 EST |
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 to do a time sheet
january wrote on March 28, 2006 23:11 EST |
| How do l put times into formula and add up the hours and time be the pay rate |
rodyzamora wrote on April 13, 2006 06:56 EST |
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] |
rodyzamora wrote on April 13, 2006 07:04 EST |
[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 |
Total hrs as 08:30 not 8.5...
hvg118 wrote on December 31, 1969 19:00 EST |
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
|
Date stamps
dplooyem@telkom.co.za wrote on December 31, 1969 19:00 EST |
| 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? |
Time Sheets
Chris wrote on December 31, 1969 19:00 EST |
| 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? |
UGH!!!
lostanfoundjo wrote on December 31, 1969 19:00 EST |
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
|
PLEASE HELP
DENISE wrote on December 31, 1969 19:00 EST |
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 |
Hours worked minus Lunch
jwcav wrote on March 17, 2007 20:05 EST |
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 wrote on March 17, 2007 20:48 EST |
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 |
Convert decimal value to time value
erwan wrote on March 18, 2007 07:30 EST |
| Please anyone could you guys help me on how to Convert decimal value to time value, tq |
oldchippy wrote on March 18, 2007 07:34 EST |
Try this,
=A1/(60*24) then format to Time |
daddylonglegs wrote on March 18, 2007 17:19 EST |
[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 |
erwan wrote on March 19, 2007 02:46 EST |
| 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 |
East of Eden (Oprah's Book Club)
