#### Hours Weekly To Hours Monthly Problem

```I maintain a database that tracks employee hours entered into time-sheets. In
essence, one column accumulates the number of hours entered daily. Another
column tells me the number of hours each employee is required to enter weekly
(for example, 40). I set up a couple of parameters (Start Date and End Date).
I can then easily collect the hours entered by an employee for a week and
compare that with his required weekly hours. An automated mail merge sends
emails notifying each employee who has missing hours.

My problem is this. I want to create a query that collects the entered hours
for a monthly fiscal closing. Months can vary. Sometimes they are three weeks
long, other times four, or even five. I want to enter the Start Date and End
Date for that monthly period and compare the total hours entered in the month
by each employee to what would be the total hours required of him for that
period. I can't figure how to sum up the required weekly hours (e.g., 40) to
the total relevant monthly hours that would be required. I don't know if I'm
clear enough on this. Can anyone help me?
```
 0
Utf
10/8/2007 2:00:03 PM
access 16762 articles. 3 followers.

6 Replies
559 Views

Similar Articles

[PageSpeed] 50

```On Mon, 8 Oct 2007 12:50:03 -0700, Ray S. <RayS@discussions.microsoft.com>
wrote:

>Actually, no. There is an entry made at the time the employee is set up that
>states the required weekly hours as 40. Some employees have a requirement of
>37 hours, others 30, still others 25, etc. Also, as I meant to make clear in
>my question, each fiscal month can vary from three, to four, to five weeks.
>I'm picking up the number of hours entered by each employee alright, and I
>can compare their weekly hours entered to their weekly required hours easily,
>too. But, when I enter the Start Date and End Date parameters to select more
>than one week, I can't figure what formula to use so that I show the
>corresponding number of hours that would be required for the parameter dates
>when the selection contains more than one week. Is this clearer?

I see this as being two separate, almost unrelated calculations. A week will
typically span more than one month, after all! You can calculate the number of
working hours during a week, and then - separately - the number of working
hours during a month. How do you want to calculate the monthly report, if the
month starts on a Friday? Do you need to calculate the hours worked Monday
thru Thursday during the previous month and count it toward that month, or
pro-rate the one day, or what???

John W. Vinson [MVP]
```
 0
John
10/9/2007 1:51:09 AM
```My first thought is that you are comparing apples and oranges. You talk
about having a column with hours entered daily and also a column with weekly
required hours. I can see where a query might have difficulty dealing with
this. Instead of a column specifying weekly required hours, how about a
column specifying daily required hours? Then you would have a column with
daily actual hours and a column with daily required hours. Seems like this
might be easier to work with.

"Ray S." <RayS@discussions.microsoft.com> wrote in message
news:B4011350-438F-44EB-8C63-B824276E2BA7@microsoft.com...
>I maintain a database that tracks employee hours entered into time-sheets.
>In
> essence, one column accumulates the number of hours entered daily. Another
> column tells me the number of hours each employee is required to enter
> weekly
> (for example, 40). I set up a couple of parameters (Start Date and End
> Date).
> I can then easily collect the hours entered by an employee for a week and
> compare that with his required weekly hours. An automated mail merge sends
> emails notifying each employee who has missing hours.
>
> My problem is this. I want to create a query that collects the entered
> hours
> for a monthly fiscal closing. Months can vary. Sometimes they are three
> weeks
> long, other times four, or even five. I want to enter the Start Date and
> End
> Date for that monthly period and compare the total hours entered in the
> month
> by each employee to what would be the total hours required of him for that
> period. I can't figure how to sum up the required weekly hours (e.g., 40)
> to
> the total relevant monthly hours that would be required. I don't know if
> I'm
> clear enough on this. Can anyone help me?

```
 0
David
10/9/2007 6:22:15 AM
```I'm working with databases that are already created so I don't have much
leeway to change the structure of tables already constructed. For whatever
reason, the original has that pesky column of required hours per week, while
the time-sheets maintain hours worked daily. I actually don't care much about
when the work week begins. What is important to me is when the financial
month closes. It always closes on Friday. So, the next financial period
begins the immediately following day - a Saturday, although our work week
really starts on Monday. Some people on call may work Saturdays and Sundays.
Yes, I see that the calculations are unrelated. The weekly calculations are
easy. I just have parameters for the Start Date and End Date: you type those
in. Then, I select the hours logged by each employee in their time-sheet from
the date greater than or equal to the Start Date and less than or equal to
the End Date. I summarize those hours grouped by employee for that period
(typically a week) and compare that sum to the required hours. If the sum is
less than the required hours, then the difference is 'missing' hours. I send
out weekly notices via a mail merge to all employees with missing h ours. As
I said, that's the easy part.

Now, again realizing that the tables give me the hours required from an
employee per week, I'm trying to set up a calculation formula that will
provide me the total results for a fiscal month. Each month will also have
parameter Start and End dates. What makes it difficult is that the logged
hours are recorded on a daily basis while the total hours in the 'required'
column remains the same every day, say 40. Simply summing them as I do with
the daily hours does not produce a result for the parameter period.

"John W. Vinson" wrote:

> On Mon, 8 Oct 2007 12:50:03 -0700, Ray S. <RayS@discussions.microsoft.com>
> wrote:
>
> >Actually, no. There is an entry made at the time the employee is set up that
> >states the required weekly hours as 40. Some employees have a requirement of
> >37 hours, others 30, still others 25, etc. Also, as I meant to make clear in
> >my question, each fiscal month can vary from three, to four, to five weeks.
> >I'm picking up the number of hours entered by each employee alright, and I
> >can compare their weekly hours entered to their weekly required hours easily,
> >too. But, when I enter the Start Date and End Date parameters to select more
> >than one week, I can't figure what formula to use so that I show the
> >corresponding number of hours that would be required for the parameter dates
> >when the selection contains more than one week. Is this clearer?
>
> I see this as being two separate, almost unrelated calculations. A week will
> typically span more than one month, after all! You can calculate the number of
> working hours during a week, and then - separately - the number of working
> hours during a month. How do you want to calculate the monthly report, if the
> month starts on a Friday? Do you need to calculate the hours worked Monday
> thru Thursday during the previous month and count it toward that month, or
> pro-rate the one day, or what???
>
>              John W. Vinson [MVP]
>
```
 0
Utf
10/9/2007 11:48:01 AM
```Quite perceptive. I tried to make that clear. I think you've got it. What you
are missing is that I cannot change the structure of the existing tables that
collect the data.

"David Portwood" wrote:

> My first thought is that you are comparing apples and oranges. You talk
> about having a column with hours entered daily and also a column with weekly
> required hours. I can see where a query might have difficulty dealing with
> this. Instead of a column specifying weekly required hours, how about a
> column specifying daily required hours? Then you would have a column with
> daily actual hours and a column with daily required hours. Seems like this
> might be easier to work with.
>
> "Ray S." <RayS@discussions.microsoft.com> wrote in message
> news:B4011350-438F-44EB-8C63-B824276E2BA7@microsoft.com...
> >I maintain a database that tracks employee hours entered into time-sheets.
> >In
> > essence, one column accumulates the number of hours entered daily. Another
> > column tells me the number of hours each employee is required to enter
> > weekly
> > (for example, 40). I set up a couple of parameters (Start Date and End
> > Date).
> > I can then easily collect the hours entered by an employee for a week and
> > compare that with his required weekly hours. An automated mail merge sends
> > emails notifying each employee who has missing hours.
> >
> > My problem is this. I want to create a query that collects the entered
> > hours
> > for a monthly fiscal closing. Months can vary. Sometimes they are three
> > weeks
> > long, other times four, or even five. I want to enter the Start Date and
> > End
> > Date for that monthly period and compare the total hours entered in the
> > month
> > by each employee to what would be the total hours required of him for that
> > period. I can't figure how to sum up the required weekly hours (e.g., 40)
> > to
> > the total relevant monthly hours that would be required. I don't know if
> > I'm
> > clear enough on this. Can anyone help me?
>
>
>
```
 0
Utf
10/9/2007 11:50:02 AM
```You can virtually change the structure of tables by creating calculated
fields in queries. You can then sum the calculated fields (usually in the
same query).

I hope these ideas help, but of course I don't know the details of what you
are doing.

"Ray S." <RayS@discussions.microsoft.com> wrote in message
news:89DC6BB1-418D-4B51-A57B-3174A048B19F@microsoft.com...
> Quite perceptive. I tried to make that clear. I think you've got it. What
> you
> are missing is that I cannot change the structure of the existing tables
> that
> collect the data.
>
> "David Portwood" wrote:
>
>> My first thought is that you are comparing apples and oranges. You talk
>> about having a column with hours entered daily and also a column with
>> weekly
>> required hours. I can see where a query might have difficulty dealing
>> with
>> this. Instead of a column specifying weekly required hours, how about a
>> column specifying daily required hours? Then you would have a column with
>> daily actual hours and a column with daily required hours. Seems like
>> this
>> might be easier to work with.
>>
>> "Ray S." <RayS@discussions.microsoft.com> wrote in message
>> news:B4011350-438F-44EB-8C63-B824276E2BA7@microsoft.com...
>> >I maintain a database that tracks employee hours entered into
>> >time-sheets.
>> >In
>> > essence, one column accumulates the number of hours entered daily.
>> > Another
>> > column tells me the number of hours each employee is required to enter
>> > weekly
>> > (for example, 40). I set up a couple of parameters (Start Date and End
>> > Date).
>> > I can then easily collect the hours entered by an employee for a week
>> > and
>> > compare that with his required weekly hours. An automated mail merge
>> > sends
>> > emails notifying each employee who has missing hours.
>> >
>> > My problem is this. I want to create a query that collects the entered
>> > hours
>> > for a monthly fiscal closing. Months can vary. Sometimes they are three
>> > weeks
>> > long, other times four, or even five. I want to enter the Start Date
>> > and
>> > End
>> > Date for that monthly period and compare the total hours entered in the
>> > month
>> > by each employee to what would be the total hours required of him for
>> > that
>> > period. I can't figure how to sum up the required weekly hours (e.g.,
>> > 40)
>> > to
>> > the total relevant monthly hours that would be required. I don't know
>> > if
>> > I'm
>> > clear enough on this. Can anyone help me?
>>
>>
>>

```
 0
David
10/9/2007 12:44:23 PM
```Please don't post such general responses. They are not at all helpful. I
tried to make myself clearer in my reply to John Vinson above, but I'm afraid
your response has just made my request get lost in the listings. I know I can
create calculated fields in queries and that I can sum those in the same or
other queries. What I need is some help in fashioning some calculation that
will total the required hours for a fiscal period stated in start and end
date parameters.

"David Portwood" wrote:

> You can virtually change the structure of tables by creating calculated
> fields in queries. You can then sum the calculated fields (usually in the
> same query).
>
> I hope these ideas help, but of course I don't know the details of what you
> are doing.
>
> "Ray S." <RayS@discussions.microsoft.com> wrote in message
> news:89DC6BB1-418D-4B51-A57B-3174A048B19F@microsoft.com...
> > Quite perceptive. I tried to make that clear. I think you've got it. What
> > you
> > are missing is that I cannot change the structure of the existing tables
> > that
> > collect the data.
> >
> > "David Portwood" wrote:
> >
> >> My first thought is that you are comparing apples and oranges. You talk
> >> about having a column with hours entered daily and also a column with
> >> weekly
> >> required hours. I can see where a query might have difficulty dealing
> >> with
> >> this. Instead of a column specifying weekly required hours, how about a
> >> column specifying daily required hours? Then you would have a column with
> >> daily actual hours and a column with daily required hours. Seems like
> >> this
> >> might be easier to work with.
> >>
> >> "Ray S." <RayS@discussions.microsoft.com> wrote in message
> >> news:B4011350-438F-44EB-8C63-B824276E2BA7@microsoft.com...
> >> >I maintain a database that tracks employee hours entered into
> >> >time-sheets.
> >> >In
> >> > essence, one column accumulates the number of hours entered daily.
> >> > Another
> >> > column tells me the number of hours each employee is required to enter
> >> > weekly
> >> > (for example, 40). I set up a couple of parameters (Start Date and End
> >> > Date).
> >> > I can then easily collect the hours entered by an employee for a week
> >> > and
> >> > compare that with his required weekly hours. An automated mail merge
> >> > sends
> >> > emails notifying each employee who has missing hours.
> >> >
> >> > My problem is this. I want to create a query that collects the entered
> >> > hours
> >> > for a monthly fiscal closing. Months can vary. Sometimes they are three
> >> > weeks
> >> > long, other times four, or even five. I want to enter the Start Date
> >> > and
> >> > End
> >> > Date for that monthly period and compare the total hours entered in the
> >> > month
> >> > by each employee to what would be the total hours required of him for
> >> > that
> >> > period. I can't figure how to sum up the required weekly hours (e.g.,
> >> > 40)
> >> > to
> >> > the total relevant monthly hours that would be required. I don't know
> >> > if
> >> > I'm
> >> > clear enough on this. Can anyone help me?
> >>
> >>
> >>
>
>
>
```
 0
Utf
10/9/2007 1:14:03 PM

Similar Artilces:

sporadic problem sending: stuck in outbox
we just changed from Sympatico to our local cable company Cogeco, we of course deleted Sympatico mail account and created Cogeco as the default. We have no others. We sometimes get our messages stuck in outbox. When we hit Send/receive we get a box that asks us if we want to use the default account as there is not a valid account for sending messages. This is puzzling.Clicking yes sends the message no problem. Is there a way to bypass this> all our account info is correct and it doesn't always occur. thanks ...

Dear Friends I am putting the same question back. I had a problem in my Store which was currupted and hence it was not doing the backup correctly due to the same my stm file started growing. So to solve the problem we have use the repair tool on the Store and Deleted the STM file and created new file. NOw the problem i am facing is in the mailboxes of users they have some emails which they can not open, Delete it gives the error message Object access failuer. I have deleted and purge one of mailbox and It is showing the correct mails when logged on to the new computer but if i log on...

Xml Serializing subclass problem
Hi guys. I've come across a problem when I tried to serialize a class into xml, only to discover that the parent class's XML Serialization properties weren't included in the output xml. Actually, the class I'm serializing is two steps down in the inheritance ladder. It's got a parent class which also has a parent class :( All those classes in the hierarchy are Xml Serializable, and I'd think that it should be obvious that all attributes/properties of the parents should be serialized for any given subclass, no ? Here's the two classes, subclass first, parent class...

Problem in CPropertySheet::DoModal
Hi, when I add more than 99 property pages, the CPropertySheet::DoModal is returning zero by which it is failing to open tabbed dialog box. Is there any limit in adding more than 99 pages? If i need to add more than 99 pages, how i can do it? Thanks and Regards, Ratna Kumar. Why would you want to have 99 pages in a Property Sheet? I think that would be very cumbersome, slow to display and very confusing for the user. "Ratna Kumar" <ratnakumar75@yahoo.com> wrote in message news:1c1d01c3867c\$591a0510\$a101280a@phx.gbl... > Hi, > > when I add more than 99 property...

Out of office / autodiscover problem
hi all, I have recently installed a new exchange 2007 server (win2008) into our domain and migrated all users mailboxes onto it. The main issue is out of office isn't working - it gives the error message "you out of office settings cannot be displayed, because the server is currently unavailable". I have read a lot of articles about this but can't seem to find the solution. If I run "test email autoConfiguration" in Outlook it finds the server (exchange2.domain.local) through SCP but fails giving error 0x80072F0C - does anyone know what this ...

Outlook 2003 Pop3 problem
I too am having the same issue. I was wondering if it is one of the outlook express patches that I downloaded because this didn't start happening until I downloaded the windows updates. Who are you replying to and what is your issue? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Michelle R" <anonymous@discussions.microsoft.com> wrote in message news:5a6701c48125\$bda840b0\$a401280a@phx.gbl... >I too am having the same issue. I was wonde...

Trial and Rebate Problems
Ordered Money Deluze 2005 online for download... Downloaded and installed, but it claims to be the trial version. The register option in the help menu has no effect on anything - doesn't respond in any way. Also never got the confirmation email that's supposed to be included for the rebate. Tried to submit an incident on the MS support site and only get: Submission Error We could not submit your support request Please click Back and try to submit your request again. Help? Thanks. rlrcstr ...

Retaining metrics for each month
What condition can be used to keep a cells evaluation status within that month even if it changes the next month. For example if A2 = "evaluated" in January but changes to "completed" in February, how can you retain that evaluated status for the month of January even after it changes during a later month? You'll need something (you???) to change the formula to a value. Depending on what you're doing, you may be able to use a worksheet_calculate event. After each calculation, the range you specify would be examined. If the formula evaluates to &qu...

Hi, I am very upset, sorry I am sure this is simple for most, but. I use excel more as a database. I do not use many formulas etc. And it serves me well. Excel 2003 why can I NOT paste a number into a cell???? without excel messing with it. ex: 123497865 I want that pasted exactly as it looks. I have tried all 4 paste options. It's driving me crazy. I want excel to do magic whebn I choose, is this not possible? Sucks really Luke Sorry, forgot. I also tried ALL formats of the cells,rows. etc. I want this I get stuff like this 7.4019E+22, not the numbers thanks Luke "Frank&quo...

Hours to minutes ??
How do I get hours & minutes to display as minutes only ? Hi Ants! Try: Format [mm] -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "Ants" <inmypants@itchybum.com> wrote in message news:40f64134\$1_1@baen1673807.greenlnk.net... > How do I get hours & minutes to display as minutes only ? > > Sorry I did not explain fully, I have 1Hr 15 minutes how can i get excel to display this as 75 minutes ?? "Norman Harker" <njharker@optusnet.com.au> wrote in message news:40f6484a\$0\$16106\$afc38c87@news.optusnet.co...

Changing working hours
I work on a rotating shift, my work hours are changing regulary. This week i am working 8:00 16:30 but next week i will be on 7:00 15:30 and the following week 11:30 20:00 The shifts dont rotate in the same pattern each month as we swap between each other to maintain personal commitments. My question is can i change my working hours in the calender every time my work hours change and not effect previous days in the calender. Also can i put in the next months expected work hours? without effecting previous days? (is it possable to have changing work hours in the calender for each day?...

TTN_NEEDTEXT problem in CView
I call EnableTooltips(TRUE) in OnInitialUpdate() in the view class. Then I handle TTN_NEEDTEXT in the view. The message map entry is ON_NOTIFY_EX(TTN_NEEDTEXT, 0, OnToolTipNeedText) This is great - I get tooltips in the view. In fact it works fine in all cases unless I have a Windows Search window open. In that case I get no tooltips in the view. It doesn't matter if the search window is buried under lots of other windows. If it's there I get no tooltips in the view. As soon as I close the search window, it all works ok again. Any ideas? Thanks Could be that the search window gr...

I'm using Outlook 2002 XP. When replying to an email the new message comes up with a system temp file automatically attached. I've been deleting the file and proceeding, but I'm not sure why it is doing this in the first place. Any toughts. ...

Hi , I am using XmlTextReader in C# code, I have one problem while reading xml file with "XmlTextReader" class. i.e I have traverse all attributs/child node of the xmlnode, After that, I want to read outerXml of my current Xmlnode. but I am unable to get outerXml node. Here I am explaining my problem with sample snippet for simplifying the understanding. <CategoryList> <Category id = "0" language = "VC++"> <Description>VC++ Articles</Description> <Active>Yes</Active> </Category> ............. <Category...

Outlook GetOccurrence Problem
Hi all, I try to use the Outlook.RecurrencePattern.GetOccurrence Function. It seems there's a really trouble about this function. You modified one of the occurrences of this element and this one does not exist any more. Close all the open elements and start again. can any one help me out here? Thanks! ...

Problems writing a linked query in SQL
Hello Newsgroup, I am doing some archive database and therefore got one table indexing every folder and one table storing which rack belongs to which department, eg: table folders : +-----------+------+-------+ | folder_id | rack | date | +===========+======+=======+ | 123456789 | 325 | 05/02 | +-----------+------+-------+ | 987654321 | 158 | 02/07 | +-----------+------+-------+ | 987485221 | 666 | 01/05 | +-----------+------+-------+ table racks: +----+------+------------+ | id | rack | department | +====+======+============+ | 1 | 158 | FKA/PKG | +----+------+------------+ | 2 |...

How do I get Outlook 2007 to print only the weeks I select?
I can't seem to get the Print Range to work in Outlook 2007. No matter what range I put in, Outlook prints a 6 week calendar (except for Feb, which it prints 5 weeks). I've tried deselecting the "print exactly one month," but that button seems to be useless for me. I have SP2 installed. I tried using the Calendar Printing Assistant, and while it will print the month out correctly, it will not print my category colors, so it is not useful for me. Bottom line is I need to see more appointments on my monthly view print out, and I can do this if I print 4 or...

Exchange problem #8
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6AB4B.78809F30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We are running Exchange server 2003 enterprise edition with SP 2 on a = Windows Server 2003 enterprise edition in a Windows server 2003 active = directory native mode. The problem is that when any user sends out a = calendar item (or invite) to user S, the calendar item goes out to = everyone in the company. Some people can not even do anything with the = item because they get a message following the cal...

2007 Runtime Problems
I have a 2003 database that has been converted to 2007 and all code compiled. All references are intact. It is fully functional as an .ACCDB using both access 2007 and runtime 2007. When converted to an ACCDE, the following errors appear: Function is not available in expressions in query expressions (Function Expression). Both in Access 2007 and runtime 2007. If I build a package and install as an .ACCDR, all functionality is restored. Since this application is constantly changing and is used by a multitude of people this deployment is not possible. Does anyone know how to create ...

Problem with sorting #2
Here is the file I have problems with: (copy and paste url in address bar) http://www.geocities.com/japly53/Moppet.xls here is an image explaining my problem: (http://img2.imageshack.us/img2/9701/excel.gif) [image: http://img2.imageshack.us/img2/9701/excel.gif] As you can see I want to sort the first names, but I want the information to stay with the name. I was wondering if you can name the range of cells then sort it by the name of the range of cells, is this possible? --Joe --- Message posted from http://www.ExcelForum.com/ Joe, Sorting only works with single-row data. You ha...

OWA Problem #22
Hi there, Several day ago, i had a problem about exchange database. I restored database from full backup and mounted the stores without any problem. Now everything works well except OWA. I can login and see all messages correctly but can't compose any new message. Also i can't see calendar. Both (new message page and callendar screen ) are blank and can't display any error. Exchange 2003 SP1 Enterprise runs on Windows 2003 Standart SP1. Any idea would be appreciated, Ozguc. ...

Outlook client and IE problem
After we installed the desktop client for outlook 2003, some options in IE (like service calendar, settings/customization and others) are no longer available when outlook is open. We have to close outlook in order to get everything back to normal in IE. Why is that happening, and what can be done about it? Hi Vsiat, This is by design. You can see some of the workarounds here: http://blogs.msdn.com/crm/archive/2006/12/11/why-the-crm-web-client-and-outlook-client-don-t-play-nicely-together.aspx HTH, Niths "vsiat" wrote: > After we installed the desktop client for outlook 2...

Need Help with Strange problem..
This evening when I open links in my Office 2003 Outlook mail program, it goes to My Documents (as well as the link I want).... How can I get this to stop? Thanks! Terri ...

Columns Problem
Hello Fellow Excel users, I have alittle problem which i hope somebody can point me in the right direction Picture my screen in excel Col A is a list of Names with Multiple entries for the same person Col B is a number ranging from 0 to 7 eg A B Joe Bloggs 1 Jim Smith 2 Joe Bloggs 4 Pete Smith 5 Jim Smith 4 What i want to create is another list where Col A is a list of names Just 1 entry per name Col B the sum of all entries for that persons name e...