Time calculations for Scheduled vs Actual Employee Time and Wages 11-28-09

I am THRYING and TRYING to make a simple schedule worksheet for a manger to 
use to calculate the time he schedules an employee to clock in and out and 
what it will cost him in regards to his allocated budget and then to be able 
to compare it after the job is completed. BUT time calculations have thrown 
me for loop and I am stuck for hours on trying to calculate time fields. 

PLEASE DEAR GOD can someone help me.

Below is a simple example of my worksheet.

A2   (Time IN)             =   1:00 PM 
A3 (Time OUT)           =   5:00 PM  
A4 (Time Worked)      =   4:00 (h:mm format)  Formula A3-A2 in 
                                             This seems to be OK BUT when I 
then 
                                              multiply the hours work by the 
hourly rate it does
                                              not work.

A5  (Hourly rate)           = $12.00 (Currency Format)

A6   (Total Paid)             = $2.00 WHEN IT SHOULD BE $48.00

I know it is calculating the dollar amount due based on .20 Time worked but 
for my life I cannot see how to make the changes necessary for this to work.	

The actual fields I am using in case it makes a difference are:

Employee Name	(Drop List)  Hutson, Gregg
Pay Rate		(Drop List) $12.00, $15.00 ect.
Assigned Position	(Drop List) such as bartender, carver, chef etc.
Scheduled IN TIME	(Drop List) 12:00 PM, 12:30 PM ect
Scheduled OUT TIME  (Drop List) 12:00 PM, 12:30 PM ect
Actual IN TIME	(Drop List) 12:00 PM, 12:30 PM ect
Actual OUT TIME	(Drop  List) 12:00 PM, 12:30 PM ect
Scheduled Hours	(h:mm)
Actual Hours	(h:mm)
Difference		(h:mm)
Scheduled Pay Amount(currency)
Actual Pay Amount	(currency)
Difference		(currency)

I am sueing Excel 2007
0
Utf
11/28/2009 11:38:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
967 Views

Similar Articles

[PageSpeed] 35

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"The Caterer" <TheCaterer@discussions.microsoft.com> wrote in message 
news:0D2D108F-F905-47D8-89D0-C653F7DBCFC8@microsoft.com...
>I am THRYING and TRYING to make a simple schedule worksheet for a manger to
> use to calculate the time he schedules an employee to clock in and out and
> what it will cost him in regards to his allocated budget and then to be 
> able
> to compare it after the job is completed. BUT time calculations have 
> thrown
> me for loop and I am stuck for hours on trying to calculate time fields.
>
> PLEASE DEAR GOD can someone help me.
>
> Below is a simple example of my worksheet.
>
> A2   (Time IN)             =   1:00 PM
> A3 (Time OUT)           =   5:00 PM
> A4 (Time Worked)      =   4:00 (h:mm format)  Formula A3-A2 in
>                                             This seems to be OK BUT when I
> then
>                                              multiply the hours work by 
> the
> hourly rate it does
>                                              not work.
>
> A5  (Hourly rate)           = $12.00 (Currency Format)
>
> A6   (Total Paid)             = $2.00 WHEN IT SHOULD BE $48.00
>
> I know it is calculating the dollar amount due based on .20 Time worked 
> but
> for my life I cannot see how to make the changes necessary for this to 
> work.
>
> The actual fields I am using in case it makes a difference are:
>
> Employee Name (Drop List)  Hutson, Gregg
> Pay Rate (Drop List) $12.00, $15.00 ect.
> Assigned Position (Drop List) such as bartender, carver, chef etc.
> Scheduled IN TIME (Drop List) 12:00 PM, 12:30 PM ect
> Scheduled OUT TIME  (Drop List) 12:00 PM, 12:30 PM ect
> Actual IN TIME (Drop List) 12:00 PM, 12:30 PM ect
> Actual OUT TIME (Drop  List) 12:00 PM, 12:30 PM ect
> Scheduled Hours (h:mm)
> Actual Hours (h:mm)
> Difference (h:mm)
> Scheduled Pay Amount(currency)
> Actual Pay Amount (currency)
> Difference (currency)
>
> I am sueing Excel 2007 

0
Don
11/28/2009 11:44:55 PM
Times in Excel are fractions of a day. So when you think you have 8 (hours), 
Excel has 1/3 (days). That's why your results are 1/24 of what you expect.

In A6, use =a4*a5*24

Regards,
Fred.

"The Caterer" <TheCaterer@discussions.microsoft.com> wrote in message 
news:0D2D108F-F905-47D8-89D0-C653F7DBCFC8@microsoft.com...
>I am THRYING and TRYING to make a simple schedule worksheet for a manger to
> use to calculate the time he schedules an employee to clock in and out and
> what it will cost him in regards to his allocated budget and then to be 
> able
> to compare it after the job is completed. BUT time calculations have 
> thrown
> me for loop and I am stuck for hours on trying to calculate time fields.
>
> PLEASE DEAR GOD can someone help me.
>
> Below is a simple example of my worksheet.
>
> A2   (Time IN)             =   1:00 PM
> A3 (Time OUT)           =   5:00 PM
> A4 (Time Worked)      =   4:00 (h:mm format)  Formula A3-A2 in
>                                             This seems to be OK BUT when I
> then
>                                              multiply the hours work by 
> the
> hourly rate it does
>                                              not work.
>
> A5  (Hourly rate)           = $12.00 (Currency Format)
>
> A6   (Total Paid)             = $2.00 WHEN IT SHOULD BE $48.00
>
> I know it is calculating the dollar amount due based on .20 Time worked 
> but
> for my life I cannot see how to make the changes necessary for this to 
> work.
>
> The actual fields I am using in case it makes a difference are:
>
> Employee Name (Drop List)  Hutson, Gregg
> Pay Rate (Drop List) $12.00, $15.00 ect.
> Assigned Position (Drop List) such as bartender, carver, chef etc.
> Scheduled IN TIME (Drop List) 12:00 PM, 12:30 PM ect
> Scheduled OUT TIME  (Drop List) 12:00 PM, 12:30 PM ect
> Actual IN TIME (Drop List) 12:00 PM, 12:30 PM ect
> Actual OUT TIME (Drop  List) 12:00 PM, 12:30 PM ect
> Scheduled Hours (h:mm)
> Actual Hours (h:mm)
> Difference (h:mm)
> Scheduled Pay Amount(currency)
> Actual Pay Amount (currency)
> Difference (currency)
>
> I am sueing Excel 2007 

0
Fred
11/29/2009 12:20:03 AM
Reply:

Similar Artilces:

calculate back dates
Hi is there a way to figure out a date 9 months previous to a date? eg. I have an install date of 12/02/10, is there a way or a formula that can count back 9 months from that install date? (they need to be weekdays) thanks very much Just use the Date function, as in: =Date(year(a1),month(a1)-9,day(a1)) Regards, Fred "sonia" <sonia@discussions.microsoft.com> wrote in message news:63DDCF12-C093-4532-B71C-2FB9112A47B8@microsoft.com... > Hi > > is there a way to figure out a date 9 months previous to a date? > > eg. I have an install date ...

Calculating a rank of scores
In the file attatched, I am needing a formula or any other kind of way to calculate the rank of each person with the set of scores. Eg. For "Person 6", I am needing a "1" to be displayed next to their name in column B to show that they are ranked number 1, BUT, if you were to change their score in column C, a different rank should be displayed, accordingly to the other 9 people in the range. I require a way to display the correct rank for each person next to their name. If anyone can help, it would be appreciated greatly. Cheers :D Attachment filename: r...

Scheduling campaigns
Newbie to MS CRM 3.0 and was trying to schedule campaigns to run on either a daily, monthly or weekly basis. Thanks in advance and apologize if I am posting to the wrong group. ...

Hello,this is my first time to post article here. #2
Hello,this is a public about money,but my english is poor,so I don't understand the article content here very much. Can anyone introduce here in simple English to me?Thanks! ...

scheduling classes
I'm trying to schedule a series of classes in Outlook 2007. They have a certain number of hours. How do I skip holidays? Can I simply make a list and they be plugged into the days? you'll need to make it outside of outlook and import. See http://www.outlook-tips.net/howto/everyx.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: ma...

Difference in Times
This is my first post so hello everyone and thankyou in advance for any assistance you can provide. The diffuclty I am having is as follows: I have a spreadsheet containing two times 8:45 and 9:45 I need to find the difference in the two times as a decimal but also as a fraction of an hour.. for these times the answer I need is 0.25 not 0.15. i.e. a quarter of an hour Can anyone help? -- bcbjork ------------------------------------------------------------------------ bcbjork's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26665 View this thread: http://...

create and print loan schedule
I would like to create a loan amortization schedule for a 96,000 dollar loan with a monthly repayment schedule of $1,800 at 6 percent interest. Is this possible with Microsoft money? In microsoft.public.money, Bruce wrote: >I would like to create a loan amortization schedule for a >96,000 dollar loan with a monthly repayment schedule of >$1,800 at 6 percent interest. Is this possible with >Microsoft money? Yes. Here is the resulting amortization table. : Loan Amortization : loan test : Payment Payment ...

Windows 2003 Server as Authoritative Time Server
Hello, How do I make my W2K3 PDC as an authoritative Time server for my domain? I have a GPS based time sync solution on my PDC. I have tried all solutions on the net, but none seems to work. The clients are not able to get any valid servers. Any help will be appreciable. Thanks Jagsrao ...

if function 12-14-09
if I have 500 rows and several col and lets say col B is office cities IE Dallas, Fort Worth, Austin, Houston and the col F has sales totals Is there a way I can subtotal the col f in setter work sheet IE if col b = Dallas, subtotal everthing that = dallas by col F You want a Pivot Table. You'll love them once you get used to them. Google pivot tables for more information. Regards, Fred "Dylan lotz" <Dylan lotz @discussions.microsoft.com> wrote in message news:AFDEAF5E-B191-45EC-8569-634F92552859@microsoft.com... > if I have 500 rows and ...

crm 3 email template, date & time
how can I add the current (system) date & time to a crm 3 email template? ...

Is this possible
Hello, I would like to have a stacked column chart to represent the following: RTL JBOS Input 30 109 Train No Train Output 134 29 What I would like is for the first stack to be called "Input" and represent RTL+JBOS and have a second stack called "Output" represent Train + No Train. The idea is that the stack "input" should be the same height a the stack "output". Given the numbers above, they are not equal and I want to illustrate the difference. Is this possible? Thank you On Fri, 1 Feb 2008, in microsoft.public.excel.charting, Maxime ...

Time Stamp #2
I need to have a cell stamped with the currant time. If I use now() the time changes every time the cell is recalculated. How can this be done? Richard When is the cell stamped? On opening/closing, printing, any change, etc? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Richard" <anonymous@discussions.microsoft.com> wrote in message news:099401c50979$91abb7d0$a401280a@phx.gbl... >I need to have a cell stamped with the currant time. If I > use now() the time changes every time the cell is > recalculated....

Folder sharing in XP Home vs. XP Pro?
Why is it that I can mount shared folders coming from my desktop running XP Pro onto my laptop running XP Home, but not vice-versa? I don't think I've done anything different between the two machines when sharing out folders. Yousuf Khan "Yousuf Khan" <bbbl67@yahoo.com> wrote in message news:4b4001f8$1@news.bnb-lp.com... > Why is it that I can mount shared folders coming from my desktop running > XP Pro onto my laptop running XP Home, but not vice-versa? I don't think > I've done anything different between the two machines when shar...

date time field for time
I need a field to store time to spend, like a task takes 5 minutes 20 seconds to finish, but not hours and minutes like clock time. I want to 10' 30" plus 5' 25" is 15' 55", but not 15' 55" later. For example, I have 2 tasks to complete one is 10' 30" and another is 5' 25". I need the sum is 2 tasks take 15' 55", but not get real clock time. Are there any field to store and calculate the infromaiton? Is it DateTime field a good choice? Your information is great appreciated, DateTime is not the appropriate choice: it...

OLE vs Attachment field
Is there ever any reason to use an OLE field rather than an attachment field, if you are not worried about backward compatibility? Attachment is a multi-valued field (MVF), so it does impose some additional restrictions on your database (e.g. some queries won't work, some code that doesn't test for fields that contains fields won't work, won't upsize, ...) However, OLE fields prior to 2007 were incredibly inefficient in storage, so not really useful anyway. A third alternative is to use a text field to store the fully qualified file name (including path and e...

No APS running at port 6400 09-02-04
Does anyone know why I would get the above message after installing Crystal 9.2.2? thanks Have you installed Crystal 9.2.2 on the CRM Server? <bmiller@fortrustsolutions.com> wrote in message news:533101c4912a$4c428ba0$a301280a@phx.gbl... > Does anyone know why I would get the above message after > installing Crystal 9.2.2? > thanks Yes I did. But then I realized that it was a mistake. I have uninstalled Crystal from the server and installed 9.2.2 on my desktop. I then installed the CRM Crystal reports enhancement. Then I open CR and then "File", "Op...

How do I create a monthly/fortnightly employee roster/timesheet?
Excel timesheet including variable start and finish times for different employees over a fortnightly period. Should be capable of charting numerous employees and indicating when they are on annual leave, on call, rostered on, over-time etc. Different coloured cells would indicate different duties ie. ROSTERED ON NORMAL DUTIES COMPLAINTS NORTH COMPLAINTS SOUTH/POUND PARKING ON CALL and leave ie. ROSTERED DAY OFF ALLOCATED DAY OFF TIME IN LIEU PUBLIC SERVICE HOLIDAY PUBLIC HOLIDAY APPROVED REC LEAVE SICK / WORKERS COMP LEAVE OTHER LEAVE A legend would be required at top of spreadsheet. Sprea...

DPC's and its schedulers in multiprocessor environment
Hi all, I have some queries on DPC's. 1> In multiprocessor environment (Thinking as 2 processors), how many DPC queues will be there? 2> In the same environment as above, How the DPC's get scheduled? Kindly let me know, Thanking you guys -- ~~~~~ Prakash A Manannavar, Bangalore/Bengaluru. why does it matter? current implementations always queue DPCs to one processor, but that is not a contract you can rely on and must assume N DPCs can run concurrently d "Prakash Manannavar" wrote in message news:E6F8C00C-D310-4815-9788-775E91857711@...

Loan Calculator A Must Look!!! #4
Excellent resource on Loan calculation: http://cash-experts.com/LC.asp ...

IF Formula 04-09-10
I have formula =min(312,200+8) and im trying to add the IF function to it as well, with the logic that if 0<312 that the text will change a certain color if true and a different color if its false. Is there a way to do this? SilviaG;695091 Wrote: > I have formula =min(312,200+8) and im trying to add the IF function to it as > well, with the logic that if 0<312 that the text will change a certain color > if true and a different color if its false. Is there a way to do this? Use conditional formatting? Functions can't change formatting itself. ...

Calculating no of work days between two dates
Please advise how to calculate the number of work days (excludin weekeends and holidays) between two date -- fluffywhitedo ----------------------------------------------------------------------- fluffywhitedoh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3528 View this thread: http://www.excelforum.com/showthread.php?threadid=55075 =NETWORKDAYS(start_date,end_date,holidays) holidays is a range of holiday dates. You will need to have the Analysis Toolpak installed, Tools>Addins, and cheek it. -- HTH Bob Phillips (replace somewhere in email address ...

Finding last accessed point of time to a list of files
Hey guys This is a "funny" one In my folder "FILES" there are lots of files.I want to check which files beeing used within a period of time (speaking of some minutes). Some - not all - of these files are used by another application (some 3D-drawing application). Now I want to check which files beeing used by this application by checking the accessed point of time. I think the application maybe work this way in order to give me some trace to follow. As a final result I will separate (informly) the used files from the not-used files in the application-session...

Transfer employees from one company to another
We use GP and have 3 companys created and fully operational. Each company have thier own payroll runs HR fully used; all 3 have the same owners. There have been a great deal of turn-around in personal and switching to-from between the companis. In GP we need to transfer employee from one company to another. Is this possible? How can we do this? Rico To my knowledge, I didnt see any option like that exists in GP. I'd suggest you to look for any third party add-ins in solution finder or contact MBS Professional Services Team which might be of help Thanks Janakiram M.P. MCP-GP On ...

Time received on Outlook 2003 different than system time.
I completed DST updates on all of our computers and used the tzmove.exe to update the calendar for Outlook 2003. The problem now is when I receive new email, the time is about 5 hrs behind the real time in the inbox. But when I double click to open the email, the time on the message is correct. I checked all time zone settings, DST settings, and calendar options. Im stuck for ideas......any suggestions?? Thanks Check the time zone setting on the Exchange server. - RAM On Mar 12, 2:52 pm, "RAM" <rmilbr...@gfnet.com> wrote: > Check the time zone setting on the Exchange s...

Using a dynamic formula to calculate results based on different assumptions #2
Hi everybody, I am looking for a simple way to resolve the following problem - don't worry if it sounds too architectural, you don't have to be an architect to solve this! I have a table of assumptions organised like this: Landuse Unit size Type 1 Type 2 Type 3............n Apt - 2bed 100m2 10% 50% 50% Apt - 1bed 60m2 20% 50% Shop 40m2 70% 50% ..... N I'd like to sum the total number of units based on the Type in another column ...