Time calculations for Scheduled Time vs. Actual Time Worked

I am 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 useing Excel 2007


0
Utf
11/28/2009 11:34:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1537 Views

Similar Articles

[PageSpeed] 35

Instead of a4 having "a3-a2" try this
=INT((A3-A2)*24)
this should get you started.
Try looking in the Excel Help pages under Multiply time.

"The Caterer" wrote:

> I am 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 useing Excel 2007
> 
> 
0
Utf
11/29/2009 8:08:02 AM
Reply:

Similar Artilces:

Timing is off by one hour
For some reason, time stamps of emails being sent, appointments, etc are all off by one hour. I have verified that the clients are all configured for central time zone. The Exchange server is set for central time zone, but for some reason, everytime anyone sends an email, the recepient shows it to be one hour behind. Also, if I setup an appointment in my calendar for 3:00pm and invite others to it, they will all show 2:00pm for appointment. Anyone have any idea on what is going on here? - Marty Check out the daylight savings checkbox on box the server and the clients. -- Martin ...

VS 2005 resource problems
Very interesting discovery. To corrupt resource file (assumes APS absent): 1. Open project. 2. Open resource from project. a. MS sees that an APS file is missing b. MS incorrectly constructs the APS file c. MS removes all languages except AFX_TARG_ENU 3. Edit and Save the resorce using the MS editor 4. All Languages removed. To preserve resource file (assumes APS absent): 1. Open resource from Windows explorer. a. MS sees that an APS file is missing b. MS correctly constructs the APS file c. MS preserves all languages. 3. ...

Linking & Emailing Multiple Work books
Hi there, Last time I was on here, you guys worked miracles....hoping for the same again ;-) Problem: I am not able to successfully send 4 workbooks by email to my boss and have the links remain active between the 4 documents that have been emailed. (They still shows link paths back to the original document location on my computer's hard drive) There is one Summary workbook linked to Workbook A, Workbook B and Workbook C. I want to be able to email the 4 documents to him, and have him be able to open up the Summary sheet, (by going to Edit > Links > Change Source, and choose the W...

Send to mail recipient not working
I am unable to attach a photo to e-mail from Windows Explorer in Vista. I use MS Outlook as my default mail. For example, with a photo, I right-click, select "Send to....Email Recipient", select size, slect "Attach", it goes through "attaching" the files but then nothing happens. A mail window does not open. I have set all my defaults to Outlook yet still nothing happens and not quite sure what to do. Any help would be greatly appreciated. Shieler -- shieler ------------------------------------------------------------------------ shiele...

One click time
I know the question has been asked and the simple answer is ctrl - shift - : to add time in a cell. Unfortunatly sometimes the easy solution is still difficult. I would like to know how to click on a cell and have the time appear. Because i do security patrols and my laptop is mounted in my vehicle. It"s difficult to type unless i stop to look at the keyboard and it's still typing with one hand. If anyone is willing to tell that would be great. I am new to excel and i am willing for trial and error. Thanks Hi, It colud be done like this. Right click your sheet tab, view code a...

ssl does not work
when accessing owa, ssl does not work, i have an error msg saying that Server object error 'ASP 0178 : 80070005' Server.CreateObject Access Error /exchweb/bin/auth/owalogon.asp, line 4 The call to Server.CreateObject failed while checking permissions. Access is denied to this object. what can i do to fix this ?? please Check that you have ASP enabled on the server: Add and Remove Programs > Add and Remove Windows Components > Application Server 'Details' > Internet Information Services 'Details' > World Wide Web Service 'Details' >...

Compact and repair does not work correctly
Compact and repair is not working as it is creating a duplicate database named db1.mdb and is not removing the previous db and renaming db1 to the old db name. I have had an open ticket with my company for 5 days now and no one seems to be able to fix the issue. Has anyone ever had this happen to them? I can run compact and repair when logged into another desktop system and have no problems, but when I try to run this on my own system (company issued laptop - IBM Thinkpad T43), it doesn't work right. Sounds as though you might not the necessary permissions to delete files from ...

What is the best work sheet used to calculate monthly expenses?
I would like to compare salary against monthly expenses and would like to know if there is such a template available. You could try: http://www3.moneysavingexpert.com/files/budget.zip Regards Trevor "Bluze" <Bluze@discussions.microsoft.com> wrote in message news:D98322DF-C811-4FFE-95CE-DA8F05A3A4EC@microsoft.com... >I would like to compare salary against monthly expenses and would like to > know if there is such a template available. ...

GetScrollBarInfo() not working with "Internet Explorer_Server" win
Internet Explorer application uses "Internet Explorer_Server" class type window to display the web pages. I want to detect whether the Horizontal Scroll bar is currently enabled/displayed or not. Using different methods we can get the handle of the "Internet Explorer_Server" the child window of Internet Explorer. I used GetScrollBarInfo () method and passed it the handle of the window with OBJID_HSCROLL as a second parameter but the function alwasy returns STATE_SYSTEM_INVISIBLE in rgstate[0] even if the Horizontal scroll bar is displayed. Using Spy++, I came t...

working with temporary working files.. #2
I keep getting the message that I cannot save a temp. working file. This work has been save on a cd and transfered back on to my desk top. Help Right-click the file, properties, see if "read only" is checked. This happens when a file is saved to a CD. Save the file on your hard drive and then copy it to the CD. Publisher does not like saving to removable media. Delete all the temporary (.tmp) files that are located in the Windows\Temp folder. For information about how to delete files, click Start, click Help, click the Index tab, type deleting files, and then click Display. --...

Calculate Form Fields
MS Word 2003 - I'm in the "Text Form Field Options" dialog box and would like to know how to calculate two form fields (texboxes) and pass the value to the third form field?. For example: textbox1 * textbox2 = textbox3 Also, is there a limit of how many form fields I can calculate? Thank you, Select Calculation as the Type in the Text Form Field Options dialog, then create your formula in the Expression box following the same rules as for an = field in ordinary text (in your example, it would be "= Text1 * Text2"). Be sure to check the box for "C...

Excel 97 quote calculations #2
Hi Jackie! You could try extending the IF statement. I don't know whether it will work in Excel 97 as I have only used Excel 2000, but here it is: =IF(B4<40000,293.75,IF(B4<80000,323.13)) and so on. Hope it works. Satyajit No basic worksheet functions have been added in recent versions, your approach would work even in Excel 4 (and probably earlier). However, you can have only 7 levels of nested functions in any version. A VLOOKUP table would be much more flexible in terms of the number of rates you could choose among. Jerry Satyajit Bhagwat wrote: > Hi Jackie! &g...

Calculate median for a Range
Hello, Please help!! I have a worksheet with multiple result rows with varying rows within each result. The result row is also above the detail, not below. What my code is doing is finding the ranges between the two result rows and caculating the median using only the two ranges after the first result row and before the last result row. I need it to perform the median on the complete range, i.e. (rng1:rng2) and not (rng1, rng2). Also, I need the result row above to be updated with the value, and not the result row below. Code and example follows. Many Thanks Michelle e.g. Column E ...

Windows Installer Runs every time I start Outlook 2003
Everytime I start my MS Office Outlook 2003 I get a Windows Installer dialog that says Preparing to install. This takes up to a minute or so before it goes away and then I can use Outlook. When researching MS online support I found a similiar problem with Visual Studio which sounds very similiar. It talks about removing a MSO.dll and then reinstalling office 2003. I do not have Visual studio .Net on my machine but I find the Mso.dll. Has anyone had a similiar problem? If so, can you help direct me in a way to get mine resolved. Thanks! ...

Word Calculations
I am trying to build a total calculation in a table - I did the calculation to =sum(above) even thought all the above totals are zero at this time - is still has a total of 12,770 - any ideas? Does it still show this value if you click in the field and press F9? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Erika" <Erika@discussions.microsoft.com> wrote in message news:B14C5FBD-9AA9-4443-A96D-47D689596E32@microsoft.com... >I am trying to build a total calculation in a table - I did the calculation...

"Send To" Menu Command Does not work with Lotus Notes
Does it work only with Outlook or MS Exchange and never with any third-party mail applications like Lotus Notes??? How do you make it work with Lotus Notes? ...

Document Conversion works to word
I have a user sending MS Works documents to work from home but MS Word can not open the document. What do I need to do to convert document? Thanks In article <987301c34625$dffb2ba0$a401280a@phx.gbl>, andruccs@monroe.army.mil says... > I have a user sending MS Works documents to work from home > but MS Word can not open the document. What do I need to > do to convert document? > Thanks > Can Works save as RTF or *.doc? -- (I may be wrong...I usually am....) Google is your Friend Email address deliberately false to avoid spam: www.gbpcomputing.co.uk ...

How do I get a true calculation?
Please explain in somewhat more detail what your requirement is. -- Kind regards, Niek Otten "reckyroo" <reckyroo@discussions.microsoft.com> wrote in message news:5577D52D-BFE3-4355-BC34-48F015A613E4@microsoft.com... | ...

Smartlist builder calculated fields
Hello, I am trying to do a datediff function in smartlist builder, and I was wondering if that is possible. Here is the code I have in the calculated field-- {*mdelotnumberinfo:Date MFG} + (datediff(d, {*mdelotnumberinfo:Date MFG} , {*mdelotnumberinfo:Date EXP} )*.5 And here is the original SQL data query that does work-- select mfgdate + (datediff(d, mfgdate, expndate)*.5) as 'Half Shelf Life' from iv00300 where mfgdate != '1900-01-01' and expndate != '1900-01-01' order by lotnumbr Any thoughts? ...

Autocalc not working
When I select a group of cells, in most of my excel docs, the sum of those cells is displayed in the bar at the bottom of the sheet. In some docs, it doesn't work. I've right-clicked on the bar and sum is checked, but nothing happens. Is this my fault? Most things are. TIA Steve In those "some" docs, the numbers are probably entered/pasted as Text, rather than numeric values. To convert them, copy a blank cell, select your 'numbers' and choose Edit/Paste Special, selecting the Values and Add radio buttons. In article <fa2gsk$oit$1@news.datemas.de>, ...

No formulas Work
For some reason none of hte formula in a spreadsheet are working. The spreadsheet is on a netowrk and when others open it, the formulas work. When I open the spreadsheet on a simple addition formula I get the #value appearing in the cell. Is there a setting in excel that I need to change. On Fri, 4 Jun 2010 21:04:36 -0700, BG Mark wrote: > > For some reason none of hte formula in a spreadsheet are working. The > spreadsheet is on a netowrk and when others open it, the formulas work. When > I open the spreadsheet on a simple addition formula I get the #value &g...

Date/Time issue with Pivot Table
Hello. I have column of data that is both date and time. However when I create the pivot table I only want it to summarize the date. I've formatted both the source and the pivot table to display on the date but formatting doesn't affect the underlying data so if a date has three different times, then the pivot table has three different entries for a given date. I can (and have) parsed out the serial number to remove the time portion but that gets a bit time consuming and this comes up frequently in different spreadsheets. I'm wondering if there's away to ...

Task Calculations
I have showing on my task list the Total Work and Actual Work fields for each task. I would like to set up a sum of all total work and a sum of all actual work without having to export to Excel. If any one has any information on how might do this it would be deeply appreciated. Thank you. Tina ...

MSOffice 2007 MVL Licence vs. Retail Licence
Hello all, I work in a company which is lucky enough to be part of the MVL scheme. I have recently installed 2 copies of MSOffice Standard accross my network using the software provided by the scheme (From the office suites and applications disk) and used the MVL licence key for our company to activate them. Since then we have purchased 2 boxed copies of MSOffice 2007 Standard - Retail edition which we were going to use to replace the licence keys used from the MVL. I have gone into regedit and attempted to use the new key from the boxed retail version but it comes up sayin...

what is the formula for calculating pertcentages of sales from mo.
Help.. Need to calculate percentage of sales growth or decline from month to month. 12/31/2004 % Change # 1/31/2005 $308,902,200 #VALUE! $250,870,126 $250,984,061 $408,321,745 Multiple tracking, how do I create a formula to accept ABS when the absolute value of the first number could be <or >. (Jan - Dec)/Dec HTH, Gary Brown "tssjhs" wrote: > Help.. Need to calculate percentage of sales growth or decline from month to > month. 12/31/2004 % Change # 1/31/2005 &g...