Time Sheet Function to Calculate

I am having some trouble trying to establish a formula that will give me the 
number of hours worked during the day given the start time say 8.30am with 
an end time of 5.30pm and say 30 minutes for lunch. For this scenario, total 
working hours would be eight and a half hours but I need Excel to display 
this result as 8.30 hours (0.30 being the minutes in the half hour) and not 
8.50. Can someone suggest what to do?

Typically the layout would be

Column A - Start Time say 8.30am
Column B - Time in minutes spent at lunch say 30 minutes
Column C - End Time say 5.30pm.
Column D - the calculating of the hours and minutes with a result of 8.30 
representing 8hours and 30 minutes as against eight and a half hours (8.50).

Thanks.




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
0
markmcd (33)
6/10/2006 7:18:26 AM
excel 39879 articles. 2 followers. Follow

5 Replies
788 Views

Similar Articles

[PageSpeed] 49

Use this formula for column D.

=C1-A1-B1/60/24

C1-A1 gives you the time difference between the start time and en
time. We then subtract the lunch break time, expressed as a fraction o
a day

--
C01
-----------------------------------------------------------------------
C01d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3442
View this thread: http://www.excelforum.com/showthread.php?threadid=55058

0
6/10/2006 8:06:19 AM
You asked this question two days ago, and I responded. What is wrong with
that suggestion

=end_time-start_time-time(0,30,0)

and format as time.

If you want it as 8.3

=INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
30,0))*24,1)*0.6

formatted as general

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" <markmcd@bigpond.net.au> wrote in message
news:1149923485_26395@sp6iad.superfeed.net...
> I am having some trouble trying to establish a formula that will give me
the
> number of hours worked during the day given the start time say 8.30am with
> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
total
> working hours would be eight and a half hours but I need Excel to display
> this result as 8.30 hours (0.30 being the minutes in the half hour) and
not
> 8.50. Can someone suggest what to do?
>
> Typically the layout would be
>
> Column A - Start Time say 8.30am
> Column B - Time in minutes spent at lunch say 30 minutes
> Column C - End Time say 5.30pm.
> Column D - the calculating of the hours and minutes with a result of 8.30
> representing 8hours and 30 minutes as against eight and a half hours
(8.50).
>
> Thanks.
>
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
=----


0
bob.NGs1 (1661)
6/10/2006 9:11:05 AM
I couldn't get it to work:

Isn't there an easier solution? There appears to be the use of range names 
which I don't have and am not sure what range should be selected. Is the 
range to be for all five days of the week only or to include the row of 
labels at the top or just the cell on the one line.

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message 
news:Oo8NN3GjGHA.4776@TK2MSFTNGP05.phx.gbl...
> You asked this question two days ago, and I responded. What is wrong with
> that suggestion
>
> =end_time-start_time-time(0,30,0)
>
> and format as time.
>
> If you want it as 8.3
>
> =INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
> 30,0))*24,1)*0.6
>
> formatted as general
>
> -- 
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Mark McDonough" <markmcd@bigpond.net.au> wrote in message
> news:1149923485_26395@sp6iad.superfeed.net...
>> I am having some trouble trying to establish a formula that will give me
> the
>> number of hours worked during the day given the start time say 8.30am 
>> with
>> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
> total
>> working hours would be eight and a half hours but I need Excel to display
>> this result as 8.30 hours (0.30 being the minutes in the half hour) and
> not
>> 8.50. Can someone suggest what to do?
>>
>> Typically the layout would be
>>
>> Column A - Start Time say 8.30am
>> Column B - Time in minutes spent at lunch say 30 minutes
>> Column C - End Time say 5.30pm.
>> Column D - the calculating of the hours and minutes with a result of 8.30
>> representing 8hours and 30 minutes as against eight and a half hours
> (8.50).
>>
>> Thanks.
>>
>>
>>
>>
>> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==----
>> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
>> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> =----
>
>
> 



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
0
markmcd (33)
6/10/2006 11:38:14 AM
There are no range names, I just used logical references as I don't know the
real ones. Substitute those with the cell references.

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" <markmcd@bigpond.net.au> wrote in message
news:1149939075_28345@sp6iad.superfeed.net...
> I couldn't get it to work:
>
> Isn't there an easier solution? There appears to be the use of range names
> which I don't have and am not sure what range should be selected. Is the
> range to be for all five days of the week only or to include the row of
> labels at the top or just the cell on the one line.
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:Oo8NN3GjGHA.4776@TK2MSFTNGP05.phx.gbl...
> > You asked this question two days ago, and I responded. What is wrong
with
> > that suggestion
> >
> > =end_time-start_time-time(0,30,0)
> >
> > and format as time.
> >
> > If you want it as 8.3
> >
> >
=INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
> > 30,0))*24,1)*0.6
> >
> > formatted as general
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Mark McDonough" <markmcd@bigpond.net.au> wrote in message
> > news:1149923485_26395@sp6iad.superfeed.net...
> >> I am having some trouble trying to establish a formula that will give
me
> > the
> >> number of hours worked during the day given the start time say 8.30am
> >> with
> >> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
> > total
> >> working hours would be eight and a half hours but I need Excel to
display
> >> this result as 8.30 hours (0.30 being the minutes in the half hour) and
> > not
> >> 8.50. Can someone suggest what to do?
> >>
> >> Typically the layout would be
> >>
> >> Column A - Start Time say 8.30am
> >> Column B - Time in minutes spent at lunch say 30 minutes
> >> Column C - End Time say 5.30pm.
> >> Column D - the calculating of the hours and minutes with a result of
8.30
> >> representing 8hours and 30 minutes as against eight and a half hours
> > (8.50).
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> > News==----
> >> http://www.newsfeeds.com The #1 Newsgroup Service in the World!
120,000+
> > Newsgroups
> >> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> > =----
> >
> >
> >
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
=----


0
bob.NGs1 (1661)
6/10/2006 12:11:14 PM
>Isn't there an easier solution?

Yes.

>> =end_time-start_time-time(0,30,0)

It's as easy as can be if you use conventional time notations like 8:30 
instead of hacking it to be 8.30.

Biff

"Mark McDonough" <markmcd@bigpond.net.au> wrote in message 
news:1149939075_28345@sp6iad.superfeed.net...
>I couldn't get it to work:
>
> Isn't there an easier solution? There appears to be the use of range names 
> which I don't have and am not sure what range should be selected. Is the 
> range to be for all five days of the week only or to include the row of 
> labels at the top or just the cell on the one line.
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message 
> news:Oo8NN3GjGHA.4776@TK2MSFTNGP05.phx.gbl...
>> You asked this question two days ago, and I responded. What is wrong with
>> that suggestion
>>
>> =end_time-start_time-time(0,30,0)
>>
>> and format as time.
>>
>> If you want it as 8.3
>>
>> =INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
>> 30,0))*24,1)*0.6
>>
>> formatted as general
>>
>> -- 
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "Mark McDonough" <markmcd@bigpond.net.au> wrote in message
>> news:1149923485_26395@sp6iad.superfeed.net...
>>> I am having some trouble trying to establish a formula that will give me
>> the
>>> number of hours worked during the day given the start time say 8.30am 
>>> with
>>> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
>> total
>>> working hours would be eight and a half hours but I need Excel to 
>>> display
>>> this result as 8.30 hours (0.30 being the minutes in the half hour) and
>> not
>>> 8.50. Can someone suggest what to do?
>>>
>>> Typically the layout would be
>>>
>>> Column A - Start Time say 8.30am
>>> Column B - Time in minutes spent at lunch say 30 minutes
>>> Column C - End Time say 5.30pm.
>>> Column D - the calculating of the hours and minutes with a result of 
>>> 8.30
>>> representing 8hours and 30 minutes as against eight and a half hours
>> (8.50).
>>>
>>> Thanks.
>>>
>>>
>>>
>>>
>>> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
>> News==----
>>> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
>> Newsgroups
>>> ----= East and West-Coast Server Farms - Total Privacy via Encryption
>> =----
>>
>>
>>
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet 
> News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ 
> Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption 
> =---- 


0
biffinpitt (3171)
6/10/2006 9:08:12 PM
Reply:

Similar Artilces:

Calculated field does not appear in subtotal
Ive added a calculated field to a pivot table to get a very basic percentage, one field divided by another. This works fine, however when i try adding a subtotal for one of the columns the calculated filed is missing. All of the other subtotals appear. (Indicated by ??? in below extract, this may not show up properly depending on tabs). Anybody come accross this before and can anyone help please? Dec Grand Total Plano Low High Plano SumLow Sum High Sum 712 1,405 69 11,112 22,617 1,057 34,786 20 19 10 157 286 159 602 2.81% 1.35% 14.49% ??? ??? ??? 1.73% 16 17 8 119 230 123 472 2.25%...

Time zone
When I noticed the clock on my pc was wrong, I changed the time zone from Pacific to Eastern which is my correct U.S. time zone. After doing this, all of the items inserted as "all day" appointments in my monthly calendar are displayed with an image of a clock on either side of the appointment. The option to display clocks is turned off in the settings. Why is this happening and how can I prevent it from happening. Right now, I've had to put back the Pacific time zone and adjust to the pc not telling the right time. Is there any other solution? Thanks! ddlaz No ...

how do you use a function in a view (access project / adp)
running access 2k adp/project as front-end; and sql-server 7 as back- end. I would like to create my own special function that I can then reference from a view. how do I do this? a function defined in a module, is apparently, not usable from a view. ie: public function myFn( x as integer ) myFn = x * 3.141 + 212 end function select *, myFn( [myTable.xvalue] ) as xAnswer from myTable TIA Create the function in SQL Server. -- Duane Hookom Microsoft Access MVP "Bob" wrote: > running access 2k adp/project as front-end; and sql-server 7 as back- ...

Send/Receive continues 1000s of times
When Outlook does its regular send/receive it ends up sending and receiving thousands of times. The task pane shows an ever increasing number of tasks that it says have been completed successfully. When all it actually is doing is rechecking the mail accounts I have, over and over again. Literally thousands of times. This interferes with anything that I want to do in Outlook while this is going on. It seems to have started after I downloaded either the latest version of itunes or the latest version of Adobe (or some other application like that). Help! And thanks. Per Roady - MVP, th...

One time code
In either an SDI or MDI application, if I want to include some functionality ( such as serial port configuration and control thread) that can only be run once per application instance, where should that code go? I looked at the App class but it doesn't handle user messages ( at least by default ). Should the main code and worker thread go in the MainFrm class in the OnCreate function? Help? Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- If it has to do with the "wi...

Reducing repetitive calculation not working
A B C 1 Fruit Qty =INDEX($1:$1048576,2,2):INDEX($1:$1048576,4,2) 2 Apple 1 3 Orange 2 4 Apple 3 Why does =SUMIF(A2:A4,"Apple",B2:B4) return the correct value ... .... while =SUMIF(A2:A4,"Apple",C1) returns a #VALUE error ? The reason I'm not including the formula in C1 as the sum_range in the SUMIF formula is to reduce the number of duplicate calculations (100,000+). I tried creating a name with the formula in C1 and using the name in the SUMIF formula as the sum_range and that works. But since the sum_range changes, I would have to use the OFFSET function to inc...

Month Function on UK dates formats (dd/mm/yyy)
Hi, (Excel 2007) I'm in the US, created 2 tabs, 1 with US dates (formatted as DATE) and the other tab with UK dates (also formatted as DATE). File sent to UK to enter dates (dd/mm/yyyy) =MONTH works find pulling out months from US dates, but fails on some UK dates. FAILS on =month(13/1/2010) #VALUE! but ok with =month(12/1/2010). I suspect it is failing because it's "out of date range". My goal is to pull out the month from all UK dates. THANK for your help! Gene It may be for than just a formatting issue. For example in A1 enter: 1/13/2010 (US format...

cpu time
Hi, Can you tell me about good tool for vs-6 that can tell me what functions in my application take the most cpu time? Thanks! In article <u$YZa3o9EHA.2680@TK2MSFTNGP09.phx.gbl>, dbg@012.net.il says... > Hi, > Can you tell me about good tool for vs-6 that can tell me what functions in > my application take the most cpu time? VS 6 includes a perfectly good profiler, which does exactly this. If you're working inside the environment, go to the link tab in the project settings, and click the "enable profiling" check box, then do a 'rebuild all' to gene...

Charts in Sheet
How come...when you change the source data...in other words...expand the data series to incorporate new data within a chart.. save changes then, exit the chart,..... the cursor moves to the top of the spreadsheet?? Why is this so? Why can't my mouse (cursor) stay in and around the chart??? Larry ...

Time Out a MsgBox
I have a file that runs a macro when it is open. This file may be opened manually or automatically by a macro from another file. If the file is opened manually I would like a MsgBox to prompt the user to end the macro before it gets into the body of the code. If the file is opened automatically I would like the MsgBox to automatically respond after a time delay to allow the macro to continue running the bulk of the code. Is there anyway to force a response to a MsgBox after a preset amount of time with no response from the user? Hi, How about this instead which closes...

Problem in BitBlt Function
Code for the program is : #include "StdAfx.h" #include "resource.h" #include ".\chidmenu.h" #define mnuHeight 24 #define mnuWidth 163 CChidMenu::CChidMenu(void) { } CChidMenu::~CChidMenu(void) { } void CChidMenu ::DrawItem (LPDRAWITEMSTRUCT lpDIS) { if(lpDIS->itemAction == ODA_DRAWENTIRE) { DrawEntireMenu(lpDIS); } //if() } void CChidMenu ::DrawEntireMenu (LPDRAWITEMSTRUCT lpDIS) { CDC* pDC = CDC::FromHandle(lpDIS->hDC); CDC *tempDC=new CDC(); tempDC->CreateCompatibleDC (pDC); CBitmap bmpMenuBitamp,*oldBitmap; BOOL b=bmpMenuBitam...

Calculating time and pay in excel
Hi, i'm trying to make a spreadsheet that can calculate pay per minute depending on an average length of time worked. Its driving me crazy and any help would be greatly appreciated! Coloumn A is length of time, at the bottom is a total time, next to that is an average length of time worked and this is then multiplied by the amount appropriate for that average. What would be the best way to work all of this out accurately? Many thanks for any help you can give Mark -- wobbleman ------------------------------------------------------------------------ wobbleman's Profile: http://www.e...

run time error
When I attempt to import a text file (.txt) in to Access, an error occurs. Run-time error �3163�: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data When I click the Debug button this pops up: .Fields("TranType").Value = rs("MoveType").Value A different error occurs when save it to my desktop, and this is what happens.Run-time error �2185�: You can�t reference a property or method for a control unless the control has the focus. When I click the Debug button this pops up: MsgBox "Import File Not Found:&quo...

Start Time & End Time
I have a table of data for my Scheduled cutting list that is exported from another program into an Excel Spreadsheet and from this table I would like to be able to find the start and end time of the production runs automatically in a separate table. The table is set up like this Planned order Time Product Description Volume 12345 08:00:00 Product 1 3500 23435 11:32:00 Product 2 5680 14567 13:24:00 Product 3 10000 and so on. I can find the start time by us...

Am I behind the times?
I have just installed Exchange sp2 on my SBS 2008 boxes and all went well. One weird thing at the end of one of the installs it said Web Apps (I think) couldn't restore default configuration, but everything seems to be working just fine. So then I went to look for updates and I see a few that I must have missed. I use WSUS on the network. One is Exchange SP2 update rollup 3. Go for it or no? Second, Windows Server 2008 SP2. Yikes, when did that come out for SBS? I wonder why it never popped up before since it looks like it's been out for quite some time. Nonetheless, s...

Excel Functions
How do I calculate the present worth of $1 utilizing an Excel function? With the correct formula, the present worth of $1, at a 10% discount rate for 5 periods (or years) would equal: yr1 .9091, yr2 .8264, yr3 .7513, yr4 ..6831, yr5 .6209. This unknown functions is different from the PV and FV functions. If I just wanted the sum of those figures, I could use the NPV function but I want to calculate the present worth of $1 each period. Thanks. Not sure what's "different from the PV" function... =PV(10%,1,0,-1) ====> 0.9091 =PV(10%,2,0,-1) ====> 0.8264 ...

Transferring freight more than one time on a sales order
We frequently have partial shipments of sales orders and we leave the sales order open until everything has shipped instead of creating backorders. The system will allow you to have several tracking numbers for each order but you can only transfer the freight once. I would like to make the suggestion to be able to transfer freight more than one time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

Incorrect Time Stamps
Exchange seems to be stamping the wrong time on emails and events sporadically. All the times on servers are correct. Any thoughts? -- Craig Tuttle - Level I Exchange Tech UCNSB.org The time stamp can also be handled by the client. Which exact time field is being stamped wrong? Have you verified that the client has the correct time (i.e. Daylight savings time set correctly)? -- Ben Winzenz Exchange MVP MessageOne "Craig Tuttle" <CraigTuttle@discussions.microsoft.com> wrote in message news:02D51926-77DC-43EB-A5B5-7A00F4530323@microsoft.com... > Exchange seems to b...

How chart time scale x-axis with another date serie
Hello all. I have this problem, I will need to create this chart that I don't know how to. Serie 1 is below which needs to be time scaled Date Count 1/1/2006 12 1/2/2006 3 1/3/2006 5 2/1/2006 18 2/3/2006 4 2/18/2006 9 4/1/2006 18 7/4/2006 38 Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with symbols. 1/31/2006 2/18/2006 4/2/2006 I will look something like you have a line of count on a time-scaled chart, with 3 dates point in the x-axis. Please ...

Can't get SUM function to work in Excel 2000
I don't know what I'm doing wrong. All I'm trying to do is add a column of numbers. In cell L13, my formula is =SUM(L2:L12). All I get back is 0. I've checked the formatting of the cells and they are all numeric. I have also tried adding cells manually, e.g., =L2+L3 -- and I get the correct result. I'm pretty sure I've used this function before without any problems. What am I doing wrong? I was having a little problem with setting the formatting. Most of my spreadsheet contains wrappable text, and I had set the style for that. Then when I tried to adjust my num...

could someone help me with design of a sheet
Hi I want to add a background to my sheet and I wonder if it possible t remove the outline of the cells? So you don't have a "light grey grid over the background -- Message posted from http://www.ExcelForum.com One way: Tools/Options/View, uncheck the gridlines checkbox. In article <mowen.17iyza@excelforum-nospam.com>, mowen <<mowen.17iyza@excelforum-nospam.com>> wrote: > Hi > I want to add a background to my sheet and I wonder if it possible to > remove the outline of the cells? So you don't have a "light grey grid" > over the backgr...

Activity default due time
I need to stop the default coming up as 12:00am and set it to something like 9am so people get reminders at reasonable times. How can this be done? thanks Adrian Adrian, If you don't mind writing a few lines of code, look at "Setting a default time in a date field" in Client Side Scripting - More JavaScript Code - Part 3 (http://www.stunnware.com/crm2/topic.aspx?id=JS12). -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup The ...

Time / Negative Time
Hi, If, for instance, in Excel you write 17:00 - 16:30, it will produc 0:30. However, 16:30 - 17:00 will produce #####, understandibly because you cannot have - ? o'clock. By formatting this to a number can produce -0.5. What if I still want to represent the decimal, but out of 60, so that can still get -0.30. I have tried messing about with fractions and still can't get it. Any ideas? Thanks, -- Message posted from http://www.ExcelForum.com On Thu, 13 May 2004 17:33:58 -0500, Kiaat <<Kiaat.167uek@excelforum-nospam.com>> wrote: >Hi, > >If, for insta...

Business Portal- Functionality Questions
Is T&E for BP linked to Project Accounting, PDK or both? How is the link formed between the modules? Is there a checklist of setup tasks available for Time and Expense for Business Portal? T&E is tied to Project Accounting, as is PDK. Prior to 9, you had to use PDK to approve the time entered via Bus Portal. Very odd design. With version 9, you enter and approve time via Bus Portal. You can use PDK, too, but all the data entry and approvals are done within PDK. Try the link for the T&E Administrator's guide. http://mbs.microsoft.com/downloads/public/BP30Docs/BP_TimeEx...

Naming charts on own sheet
Hi. I have a series of charts (which are all contained on their own sheets). I need to name each of the charts (as they will be used by someone else in a macro). I have tried clicking on them and also pressing shift before clicking on them, and I am not able to change the name in the name combo box. Can anyone advise me of how I can change the names. Thanks for your help. Hi, If you have chart sheets you can change the name by simply changing the sheet tab name. What you described is the method used on chartobjects, which are usually on a worksheet. Cheers Andy -- Andy Pope, Mi...