how do i calculate a work schedule in excel?

I am so close to completing a scheduling worksheet, but have a problem with 
one of the formulas. I have created dropdowns for 'in' and 'out', using AM & 
PM for the times, a formula to calculate the daily number of hours and a 
formula to calculate the total number of hours scheduled for an entire week. 
My problem is that I want to deduct lunch periods from the daily schedule, 
but have been unable to figure out that calculation. I want to be able to 
deduct :30, :45, and :60 minute lunches. At this point, I would even settle 
for the ability to deduct a fixed time period of :30 minutes. Any suggestions 
are very much welcome!!!
0
Paul297 (622)
12/7/2005 10:49:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
238 Views

Similar Articles

[PageSpeed] 34

Hi Paul,

If you have the begin time in a1, lunch start in b1, lunch end in c1,
and end of day in d1.

Something like this should work in e1

=((D1-A1+(D1<A1))-(C1-B1+(C1<B1)))*24

Make sure to use this type of time format 15:30 instead of 3:30

Hope this helps

Larry


-- 
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287
View this thread: http://www.excelforum.com/showthread.php?threadid=491660

0
12/7/2005 11:31:40 PM
Paul

Perhaps Chip Pearson's site on timesheets will give you the rest of what you
need.

http://www.cpearson.com/excel/overtime.htm


Gord Dibben Excel MVP

On Wed, 7 Dec 2005 14:49:03 -0800, "Paul" <Paul@discussions.microsoft.com>
wrote:

>I am so close to completing a scheduling worksheet, but have a problem with 
>one of the formulas. I have created dropdowns for 'in' and 'out', using AM & 
>PM for the times, a formula to calculate the daily number of hours and a 
>formula to calculate the total number of hours scheduled for an entire week. 
>My problem is that I want to deduct lunch periods from the daily schedule, 
>but have been unable to figure out that calculation. I want to be able to 
>deduct :30, :45, and :60 minute lunches. At this point, I would even settle 
>for the ability to deduct a fixed time period of :30 minutes. Any suggestions 
>are very much welcome!!!
0
Gord
12/7/2005 11:51:15 PM
Thanks Larry, but I should have mentioned that all our managers want to see 
is the start and end times for each day. We have set lunch periods, but not 
at specific times during the day. Thanks again though for your quick 
response!!!

Paul

"keithl816" wrote:

> 
> Hi Paul,
> 
> If you have the begin time in a1, lunch start in b1, lunch end in c1,
> and end of day in d1.
> 
> Something like this should work in e1
> 
> =((D1-A1+(D1<A1))-(C1-B1+(C1<B1)))*24
> 
> Make sure to use this type of time format 15:30 instead of 3:30
> 
> Hope this helps
> 
> Larry
> 
> 
> -- 
> keithl816
> ------------------------------------------------------------------------
> keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287
> View this thread: http://www.excelforum.com/showthread.php?threadid=491660
> 
> 
0
Paul297 (622)
12/7/2005 11:55:02 PM
Reply:

Similar Artilces:

Can i use Windows Search to find a number within Excel?
If i use Widnows search of the CONTENTS of files (not file names), i find in Excel, that for cells that contain text, they can be found. But, it does not appear to me that numbers can be found. Of course the number as Excel stores it may differ from how it is displayed. But, does anybody know if this is possible? For instance, i have a cell with the number 3,709,227 in it, as it appears in Excel (as formatted). I tried typing the number both with and without the commas, into WIndows search, but Windows search still does not find the Excel file that contains that number. So, any kn...

Excel 2007: Problem with refresh screen (when minimizied)
Hello On our network (around 40 workstations; Win XP Pro and Office 2007) we have particular the following problem: The user minimized the Excel programm window and do some other works in other applications, return to the minimized Excel window and want to maximize the Excel window. Result: The Excel window is empty. You can paricular see some rows or colums but often not. We try out also other graphic card but get no success. Any ideas? Kind regards, Martin Schweizer ...

excel 97 filename truncation in Windows XP Pro
I have a legacy application that runs in Excel 97. I have Excel 97 and Excel XP loaded on a new machine that runs Windows XP Pro. The legacy application fails because the filename of a workbook is truncated during the saveas command. When I do a manual saveas in Excel 97, the filename truncates to the first letter. If I do the same thing in Excel XP, the saveas works normally. There have been a few posts describing the same problem. I searched google and found this: http://google.com/groups?threadm=020101c30916%2442320250%24a501280a%40phx.gbl The last post in the thread suggested an answe...

highliting works on with html format?
Hi guys, Here is what I would like to do: In outlook, suppose I get a message in plan text format. I would like to open the email, and maybe make some sentences bold and colored. and then I can save it. Hence after sometime the email will be noticable for me. Now, I can do this if the e-mail is sent in html format cause then I open the message and then I press "edit message" and the edit toolbar becomes usable and I do things on the email. However, if the email is sent in "plain text" format, I can not edit the email at all. I try to push the "edit message" butt...

Exchange 2003 OWA stopped working and DNS Events
Hi All, I am struggling with yet another Exchange 2003 problem. It has been working flawlessley for almost, nothing changed and all of a sudden quit working. When I try to open up my OWA I am getting the 503 service unavailable page. In my Event Logs, I am seeing this when I reboot the server: The dynamic registration of the DNS record '_ldap._tcp.Default-First-Site-Name._sites.ForestDnsZones.ic-MYDOMAINXXXX.us. 600 IN SRV 0 100 389 exchange.MYDOMAINXXXX.us.' failed on the following DNS server: DNS server IP address: 205.178.190.31 Returned Response Code (RCODE): 5 Returne...

Office 2003 Opening a new Excel Doc.
I just updated to Office 2003 and everytime I open up a new workbook a blank workbook name PERSONAL opens as well. How do I change that from happeneing?? Thanx, Jesse Hi Jesse Window>Hide and save the personal.xls When you record a macro you have the option to save the macro in this hidden workbook so you can use the macro in all your workbooks. You can find the file in your Xlstart folder -- Regards Ron de Bruin http://www.rondebruin.nl "Jesse" <Jesse@discussions.microsoft.com> wrote in message news:65505D55-31EB-4BAA-A32D-B052F70E03AD@microsoft.com... >I just...

Two Excel tables in MS Query
How can I link two tables in two Excel-files, in MS Query and result view in PivotTable? ---------------------------------------------------------------------- I can link two tables in one Excel-file. ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _ "SELECT `TKG_DATA$`.YEAR, `TKG_DATA$`.MONTH, `TKG_DATA$`.DAY, `TKG_DATA$`.TIME, `TKG_DATA$`.`INC/OUT`, `TKG_DATA$`.`C59/170`, `TKG_DATA$`.`C46/158`, `TKG_DATA$`.`C356/137`, `TKG_DATA$`.`C355/358`, `TKG" _ , _ "_DATA$`.`C354/357`, `TKG_DATA$`.`C32/142`, `TKG_DATA$`.`C30/116`, `TKG_D...

Working offline, but no exchange server to go online with.
Hi, I use: Win XP SP2, Office 2003, SP 2. I started to create an Exchange Server account but shortly after, deleted it. Since then, I get an "Offline" icon in the right lower corner of the Also, when I try to use Rules and Alerts, I get the message: "You are currently working offline. To use this command, you must go online and connect to the Exchange Server." I do not have Exchange Server. There is no choice to go online in File Menu either. Any help would be appreciated. Create a new mail profile in Control Panel->Mail Icon->Profiles. = Configure it with...

how to quick calculate sum of colume A1 to A115?
How to quick calculate sum of colume A1 to A115? Or, do I always have to set up formular =sum(A1:A15) to get the sum? =SUM(A2:OFFSET(A2,MATCH(999999,A:A,1),1)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Anthony B" <no.spam@no.spam.com> wrote in message news:O6%23S2HZTIHA.5016@TK2MSFTNGP06.phx.gbl... > How to quick calculate sum of colume A1 to A115? > > Or, do I always have to set up formular =sum(A1:A15) to get the sum? On Wed, 2 Jan 2008 17:36:23 -0600, "Don Guillett" <dguillett1@austin.rr.com> wrote: >...

Merging excel office data into a publisher office project(Australi
Having problems when I get to the point in the merge wizard "catalog merge template", with the DOB format to be entered into the membership card that I have made in Publisher. It reverts back to American date format. Could the problem be in the edit record section? All seems to be okay till then. I have checked the regional settings etc., all okay. When I have finished template of merge & highlighted the DOB field gone to language on tool bar and set language it still doesn't work then preview, it has reverted to American. ...

how do I tell excell 2003 to print sheets back to front?
In excel 97 I could tell printer to start printing from the back page to the front so the job would come off of the printer in the right order. I cannot find that command in excell 2003. Am I overlooking it, or have they changed the command convention to an obscure, hard to understand, jargon. FILE | PRINT... | Click on the PROPERTIES button | PAGE ORDER, i.e. back to front. "goochiepepper" wrote: > In excel 97 I could tell printer to start printing from the back page to the > front so the job would come off of the printer in the right order. I cannot > find that co...

How can i get more 3D shapes for Auto shapes in excel?
Please suggest how i can get more shapes (simple as well as 3D ) in Auto Shapes of drawing toolbar in excel & word? ...

Export Excel file to SQL
I'm currently doing a project where it require client to export excel spreadsheets to SQL. the application that is using the excel spreadsheet exported is a web-based application. Is it possible to do that? Any suggestion or recommendation is greatly appreciated. thank you Go to Enterprise Manager. Browse to your database in the left pane (object browser). Right click your database. All Tasks --> Import Data. Next. Set your datasource to Microsoft Excel 97-2000 or whichever Excel version you have. Click the ... button next to the File Name text box and browse to your Excel sheet. M...

Excell Cells: Auto Formatting
I have protected a workesheet and unfortunately when the people submit information on the cells and it is too long than the actual height of the row and it wont extend. What can I do? When protecting the sheet allow users to format cells and rows. Gord Dibben MS Excel MVP On Tue, 9 Feb 2010 13:14:01 -0800, Sophie <Sophie@discussions.microsoft.com> wrote: >I have protected a workesheet and unfortunately when the people submit >information on the cells and it is too long than the actual height of the row > and it wont extend. What can I do? How do I jus...

autorecover doesn't work reliably during a forced reboot
Scenario: - Auto-recover is enabled in excel - An Unsaved Excel document is open - An SMS process for applying security or windows update patches causes a forced/unattended system reboot After the reboot is performed, the unsaved changes are lost in the excel document, because Autorecover doesn't think there is an autorecover document (apparently). I've had this happen a number of times, but it isn't 100% consistent. Is there anything I can do to increase the likelihood that autorecover will actually work (short of trying to disable SMS and windows update)? Is it possi...

How do I create a drop down list in Excel 2003
How do I create a drop down list in Excel 2003. I've tried to use the help menu and I cannot figure it out. See: http://www.contextures.com/xlDataVal01.html -- Regards, Peo Sjoblom "Lenny" <Lenny@discussions.microsoft.com> wrote in message news:8F05DEA1-E264-4760-9DDE-3C97319AA1D4@microsoft.com... > How do I create a drop down list in Excel 2003. I've tried to use the help > menu and I cannot figure it out. ...

Watermark in excel 2000
Hi I'm trying to insert a watermark from a jpg file. The probem is the watermark always stays on top and cover the text, no matter I use the "send to back" button on or the item. TIA /Per Hi maybe the following sites will help you: http://www.xl-logic.com/pages/formulas.html http://www.rondebruin.nl/files/Watermark.zip And for Excel 2002+: http://cpap.com.br/orlando >-----Original Message----- >Hi > >I'm trying to insert a watermark from a jpg file. >The probem is the watermark always stays on top and cover the text, no >matter I use the "send ...

Calculation problem where a fixed charge is involved
Forgive me for asking guidance on something which is probably quite simple. I've set up a worksheet to calculate all the hidden costs of on-line auctions and it is the PayPal aspect that is giving me grief. PayPal charge me 3.4% + 20p per transaction. If I use: =SUM(B2*0.034+0.2)and C2 and D2 and so on... ....it obviously gives me the right answer but, if I just enter the formula at the beginning of posting sales and before any purchase or bid has been made, when I want a nil balance to show, this method shows 20p. How do I amend the formula for that aspect so that the 20p set c...

Maximum value to use autofiler feature in excel
Hi, does anybody know the maximum value to use autofilter in ms excel? I heard my friend told me that excel could filter only select data is less than 1000. He told me that he has 4000 row and use autofilter, an expected record is more than a thousand but the record display only 1000 maximum. I want to test like him too but I don't have any file that contain alot many thousand of record. Does anybody have any idea? Hi see: http://www.contextures.com/xlautofilter02.html#Limits -- Regards Frank Kabel Frankfurt, Germany "Roath Kanel" <012633023@mobitel.com.kh> schrie...

Excel 2000 #38
Hi - I use Excel 2000 and wish to display negative number, accounting and currency values in parentheses. However in the Format Cell function in Excel there is no option to use parentheses. The only option is to use the -ve symbol or to display the value in red. How do I display the value in parentheses. -- Mike from Australia Hi Mike Try Format>Cells>Number>Custom $#,##0.00;[Red]($#,##0.00) Regards Roger Govier Mike wrote: > Hi - I use Excel 2000 and wish to display negative number, accounting and > currency values in parentheses. However in the Format Cell functio...

Excel 2003 ODBC Add-in
Where can I download Excel 2003 ODBC Add-In from ? Hi MS no longer delivers this addin with Excel 2003. But check out: http://www.bygsoftware.com/examples/sqlrequest.html http://office.microsoft.com/downloads/2002/xlodbc.aspx -- Regards Frank Kabel Frankfurt, Germany "Excel2003User" <Excel2003User@discussions.microsoft.com> schrieb im Newsbeitrag news:687E3600-63A2-414E-A7B8-5366BE386667@microsoft.com... > Where can I download Excel 2003 ODBC Add-In from ? ...

Calculating days and dates
I need to calculate the number of working days (ie Monday to Friday) from the date entered in cell D4 (22/07/2005). This needs to update each day in cell E4. I can do this not based on working days, by entering =DATEDIF(D4,TODAY(),"d") but where in the formula do I reference it to working days or network days? HELP! -- HDV ------------------------------------------------------------------------ HDV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26299 View this thread: http://www.excelforum.com/showthread.php?threadid=395788 You can use the ...

DDE link error Excel freezes
Hi, I am using Excel 2003 and in one sheet I have a DDE link from anothe program. When I open the sheet I get a question wether I want to updat the fields or not. When I click YES the sheet normally opens and I ca view the data. But sometimes it seems like Excel freezes and I have t wait 3-5 minutes until it resumes. Anyone that has any idea why? The source program is always started first by the way. Kennet -- Message posted from http://www.ExcelForum.com ...

Excel 2003, Opening a file
Hello Everyone, I have this problem when trying to open a file in Excel. It takes along time to drill down down in the folder list. Any ideas? Thanks -Mark I suggest putting a shortcut to the file(s) or to the buried folder that your file(s) are in. The shortcut could be on your desktop or in some folder close to the "surface". To create the shortcut or your desktop you can navigate to the desired folder (using My Computer), right click and drag the folder to your desktop. When you release respond that you want to create a shortcut (rather than moving or copying...

Excel cell display
I want a text box to display when I click on a particular cell - how can I make that happen? Would Insert> Comment work for you? "slantt" wrote: > I want a text box to display when I click on a particular cell - how can I > make that happen? Yes, thanks - I found that after I posted of course (newbie to Excel). I was looking to see if there was a macro I could gen to do the same thing... "ufo_pilot" wrote: > Would > Insert> Comment > work for you? > > > "slantt" wrote: > > > I want a text box...