Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
I am putting together a simple excel spreadsheet to calculate hourly
schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
Hi
To get the calculation for Monday =MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) Using MOD() rather than just subtracting B1 from C1 will deal with any occurrences of times running over from 1 day to the next. Deducting TIME(1,0,0) is being multiplied by the text to ensure you have both start and end times, so the formula does not deduct an hour for any days not worked. Repeat procedure for other days and sum results. The cell containing the formula that sums your intermediate calculations would need to use FormatCellsNumberCustom[h]:mm in order to sum past 24 hours. -- Regards Roger Govier "RJ Swain" <RJ wrote in message ... I am putting together a simple excel spreadsheet to calculate hourly schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2 from this
and still work? "Roger Govier" wrote: Hi To get the calculation for Monday =MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) Using MOD() rather than just subtracting B1 from C1 will deal with any occurrences of times running over from 1 day to the next. Deducting TIME(1,0,0) is being multiplied by the text to ensure you have both start and end times, so the formula does not deduct an hour for any days not worked. Repeat procedure for other days and sum results. The cell containing the formula that sums your intermediate calculations would need to use FormatCellsNumberCustom[h]:mm in order to sum past 24 hours. -- Regards Roger Govier "RJ Swain" <RJ wrote in message ... I am putting together a simple excel spreadsheet to calculate hourly schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
(C2-B2)+(C2<B2)-2/24 or (1/12) ..since it uses the 24 hour cycle, just
subtract the fraction of the day you want to. So if you needed to subtract 6 hours it would be (C2-B2)+(C2<B2)-6/24 (or reducing the 6/24 to 1/4). Probably not the cleanest or most efficient but it works. "RJ Swain" wrote: If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2 from this and still work? "Roger Govier" wrote: Hi To get the calculation for Monday =MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) Using MOD() rather than just subtracting B1 from C1 will deal with any occurrences of times running over from 1 day to the next. Deducting TIME(1,0,0) is being multiplied by the text to ensure you have both start and end times, so the formula does not deduct an hour for any days not worked. Repeat procedure for other days and sum results. The cell containing the formula that sums your intermediate calculations would need to use FormatCellsNumberCustom[h]:mm in order to sum past 24 hours. -- Regards Roger Govier "RJ Swain" <RJ wrote in message ... I am putting together a simple excel spreadsheet to calculate hourly schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
Hi
There was a mistake in my formula, it should have been =MOD(C1-B1,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) However, if you do not have times spanning 24 hours, then c5-b5 etc will work OK. =(C5-B5)+(E5-D5)+(G5-F5)+(I5-H5)+(K5-J5)- (TIME(1,0,0)*(COUNT(B5:K5)/2)) counting the values entered between B5 and K5 and dividing by 2 will give the number of days worked which multiplied by TIME(1,0,0) will give the number of hours to be subtracted. I might be inclined to multiply the whole thing by (ISEVEN(COUNT(B5:K5)) which will give a result of 0 if there aren't matching pairs of times for each day. If you had 17:00 entered in G5 and nothing in F5 it would count 17 hours, and subtract 0.5 hour for lunch. -- Regards Roger Govier "RJ Swain" wrote in message ... If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2 from this and still work? "Roger Govier" wrote: Hi To get the calculation for Monday =MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) Using MOD() rather than just subtracting B1 from C1 will deal with any occurrences of times running over from 1 day to the next. Deducting TIME(1,0,0) is being multiplied by the text to ensure you have both start and end times, so the formula does not deduct an hour for any days not worked. Repeat procedure for other days and sum results. The cell containing the formula that sums your intermediate calculations would need to use FormatCellsNumberCustom[h]:mm in order to sum past 24 hours. -- Regards Roger Govier "RJ Swain" <RJ wrote in message ... I am putting together a simple excel spreadsheet to calculate hourly schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
Touche' Roger! Thank you for your insight but now a twist.. using the simple
formula of (c5-b5) then the formula with (time) is there a way to have it not subtract if the total for that day is less than 8 hours? So if someone works 5 hours that day it will not calculate or subtract an hour for lunch? "Roger Govier" wrote: Hi There was a mistake in my formula, it should have been =MOD(C1-B1,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) However, if you do not have times spanning 24 hours, then c5-b5 etc will work OK. =(C5-B5)+(E5-D5)+(G5-F5)+(I5-H5)+(K5-J5)- (TIME(1,0,0)*(COUNT(B5:K5)/2)) counting the values entered between B5 and K5 and dividing by 2 will give the number of days worked which multiplied by TIME(1,0,0) will give the number of hours to be subtracted. I might be inclined to multiply the whole thing by (ISEVEN(COUNT(B5:K5)) which will give a result of 0 if there aren't matching pairs of times for each day. If you had 17:00 entered in G5 and nothing in F5 it would count 17 hours, and subtract 0.5 hour for lunch. -- Regards Roger Govier "RJ Swain" wrote in message ... If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2 from this and still work? "Roger Govier" wrote: Hi To get the calculation for Monday =MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) Using MOD() rather than just subtracting B1 from C1 will deal with any occurrences of times running over from 1 day to the next. Deducting TIME(1,0,0) is being multiplied by the text to ensure you have both start and end times, so the formula does not deduct an hour for any days not worked. Repeat procedure for other days and sum results. The cell containing the formula that sums your intermediate calculations would need to use FormatCellsNumberCustom[h]:mm in order to sum past 24 hours. -- Regards Roger Govier "RJ Swain" <RJ wrote in message ... I am putting together a simple excel spreadsheet to calculate hourly schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Schedule hours calculation
Hi
Now I think we need formulae on a cell by cell basis, and it might be easier to convert to decimal format first. Excel store time as a fraction of a day, so multiplying by 24 gives decimal hours result, with the cell being formatted as General. So =(C5-B5)*24-((C5-B5)*245) where the final 5 is the number of hours worked, above which you want to subtract an hour. I would insert a series of columns at D etc. (which would change all other locations) and in D5 enter the above formula. Then my summary would be D5+G5+J5+M5+P5 Having set the formulae and copied down, I would hide those columns with the calculation. -- Regards Roger Govier "RJ Swain" wrote in message ... Touche' Roger! Thank you for your insight but now a twist.. using the simple formula of (c5-b5) then the formula with (time) is there a way to have it not subtract if the total for that day is less than 8 hours? So if someone works 5 hours that day it will not calculate or subtract an hour for lunch? "Roger Govier" wrote: Hi There was a mistake in my formula, it should have been =MOD(C1-B1,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) However, if you do not have times spanning 24 hours, then c5-b5 etc will work OK. =(C5-B5)+(E5-D5)+(G5-F5)+(I5-H5)+(K5-J5)- (TIME(1,0,0)*(COUNT(B5:K5)/2)) counting the values entered between B5 and K5 and dividing by 2 will give the number of days worked which multiplied by TIME(1,0,0) will give the number of hours to be subtracted. I might be inclined to multiply the whole thing by (ISEVEN(COUNT(B5:K5)) which will give a result of 0 if there aren't matching pairs of times for each day. If you had 17:00 entered in G5 and nothing in F5 it would count 17 hours, and subtract 0.5 hour for lunch. -- Regards Roger Govier "RJ Swain" wrote in message ... If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2 from this and still work? "Roger Govier" wrote: Hi To get the calculation for Monday =MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2) Using MOD() rather than just subtracting B1 from C1 will deal with any occurrences of times running over from 1 day to the next. Deducting TIME(1,0,0) is being multiplied by the text to ensure you have both start and end times, so the formula does not deduct an hour for any days not worked. Repeat procedure for other days and sum results. The cell containing the formula that sums your intermediate calculations would need to use FormatCellsNumberCustom[h]:mm in order to sum past 24 hours. -- Regards Roger Govier "RJ Swain" <RJ wrote in message ... I am putting together a simple excel spreadsheet to calculate hourly schedules for my employees. What I would like it to do is calculate the hours of the week and subtract 1 hour per day for lunch hours. So it is setup as follows A1 - Name B1 - Start C1 - Finish (Monday) (-1 hour lunch) D1 - Start E1 - Finish (Tuesday) (-1 hour lunch) etc.. N1 - Total hours for the week |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working days and hours calculation - request assistance please! | Excel Worksheet Functions | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
Calculation of full hours | Excel Worksheet Functions | |||
How can I make an Employment Schedule that Tabulates hours worked. | Excel Worksheet Functions |