Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Working days and hours calculation - request assistance please! sbickley Excel Worksheet Functions 0 August 16th 06 06:37 PM
Calculation of hourly rate times hours times 1.5 Newbusinessbod Excel Worksheet Functions 1 December 6th 05 05:44 PM
adding rows of hours and minutes to get a total Tipps Excel Worksheet Functions 1 November 4th 05 08:03 PM
Calculation of full hours [email protected] Excel Worksheet Functions 1 September 8th 05 06:26 PM
How can I make an Employment Schedule that Tabulates hours worked. Alicia Excel Worksheet Functions 1 December 27th 04 04:07 AM


All times are GMT +1. The time now is 12:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"