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

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

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

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

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

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

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