Time differances

1 have 4 boxes 

start time        end time        lunch          hours worked
09:00               17:00             1.00              7:00
09:00               17:00             0.30              7:70        (should 
be 7:50)

(B3-A3)*24-C3

How do I make it 9.5 hours by changing forumla

Thanks
0
TreverB (3)
8/17/2005 3:46:24 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
454 Views

Similar Articles

[PageSpeed] 27

Trever,

=(B3-A3)*24-C3*24

Format for General, Number, or anything except Date/Time. Format - Cells - 
Number.

You'll get 7.5.  If you got 7:50, as you requested, it would look like hours 
and minutes.
--
Earl Kiosterud
www.smokeylake.com

"Trever B" <TreverB@discussions.microsoft.com> wrote in message 
news:CA55486C-DBB3-4A19-A54A-866D17D9A9E3@microsoft.com...
>1 have 4 boxes
>
> start time        end time        lunch          hours worked
> 09:00               17:00             1.00              7:00
> 09:00               17:00             0.30              7:70 
> (should
> be 7:50)
>
> (B3-A3)*24-C3
>
> How do I make it 9.5 hours by changing forumla
>
> Thanks 


0
someone798 (944)
8/17/2005 3:54:21 AM
Hi Trevor,
Time is entered using colons to separate  minutes from hours,
DO NOT USE A DECIMAL POINT where you should be using a colon.
A value of  1.0  represents  24 hours not one hour..
A value of   .3  represents   7 hours 12 minutes

The formula to return  decimal hours is:,  formatted as 0.00
 c2:        =(b2-a2+(a2>b2))-c)*24
and fill down.with the fill handle.

If you format the result as time     [h]:mm
  The logical expression (A2-b2)  will return either 0 or 1,
  where  a value of 1 represents 24 hours (1 day) and is necessary
  if hours go through midnight.

The format of  [h]:mm   prevents hours from rolling off into days
and not being shown,  you will probably need that format for
the total number of hours,  if shown as an  Excel time.

For more information on Date and Time see
  http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Trever B" <TreverB@discussions.microsoft.com> wrote in message news:CA55486C-DBB3-4A19-A54A-866D17D9A9E3@microsoft.com...
> 1 have 4 boxes
>
> start time        end time        lunch          hours worked
> 09:00               17:00             1.00              7:00
> 09:00               17:00             0.30              7:70        (should
> be 7:50)
>
> (B3-A3)*24-C3
>
> How do I make it 9.5 hours by changing forumla
>
> Thanks


0
8/17/2005 10:34:33 AM
Reply:

Similar Artilces:

"Time oldest message submitted" column in Exchange Queue
What does this column represent? For example: I have some items in queue under the column "time oldest message submitted" dated 9/23/06 and then when you double click on the queue and click find now the date of the earliest message it is 9/26/06. Where does the date in the "time oldest message submitted" come from? I thought maybe it was the first time an email for that domain appeared in the queue but that doesn't seem right. One more thing that I should probably mention is that the date discrepency is only happening with items that are for domains that appear to be...

Help: Time-out or Lock-up During Update (Money 2004)
Most of the time, the update would not go through, either quotes, banking, or Money update. The connection is good. Using Money 2004 Deluxe. Sometimes it does go through..... Help! Help! Help! Help! In microsoft.public.money, Andrew wrote: >Most of the time, the update would not go through, either quotes, banking, >or Money update. >The connection is good. Using Money 2004 Deluxe. Sometimes it does go >through..... > You could consider background banking. That way the updates would be in the background. I'll have to return it. It is so sad that most of the vendors ju...

Saving large spreadsheets takes a long time.
I hope somebody can help answer my questions... First off... the way I understand Excel saves files is that Excel saves to a temporary file first and when the temporary file is sucessfully saved, the original is deleted and the temporary file is renamed to the original file name. Now with a very large file, 400MB+, creating this temporary file, deleting the original, and renaming the temporary can take a long time, even with a higher end PC (P4, 2.0 Ghz w/ 1GB RAM). Correct? Now I have a user who is doing the above and is a bit "miffed" about the amount of time it tak...

CRM 4.0 Redeployment
Hello, How is it possible to install CRM and connect to existing DB when you want to install on a new OU, when you're never asked which OU to use? U need to provide complete information : CRM Version : Type = professional , enterprise..... If you are using enterprise edition then u can import it (in CRM 4.0). In other cases organizationsunit name should be same as MSCRM database name. ------ Aamir Blog = http://mscrmsupport.wordpress.com/ Trond: 1. Install CRM 4.0 as a 'new' installation, choose the OU you want during the installation. when it asks for an organization n...

formatting time #2
How do I take off the seconds in my time-formatted cells? They look ok in the worksheet (h:mm) but in the formula bar and when I merge the data into WORD, the seconds show up. I also created a Custom format and that didn't work. Hillary You can format the merge field in Word. Right-click the field and after the field name type \"hr:mm" Regards Peter >-----Original Message----- >How do I take off the seconds in my time-formatted cells? >They look ok in the worksheet (h:mm) but in the formula >bar and when I merge the data into WORD, the seconds show >up. I...

real-time camera feed video on a GUI
Hi, I want to display real-time camera feed video on a MFC GUI. If so does anyone know of any webcam manufacturers who provides APIs to their camera product that would do this via USB for example or maybe I was think of a webcam with static IP which can be viewed with a browser and then displaying that browser image on the GUI. I welcome any suggestions. Thanks. You can use DirectX to do it. Take a look at this for some help http://www.codeproject.com/directx/LiveVideo.asp AliR. <ratcharit@gmail.com> wrote in message news:1140188342.013426.33690@z14g2000cwz.googlegroups.com... >...

Rollup 6 Server and Client can be installed at different times, ri
It's my understanding that I can install Rollup 6 server, then later (days? weeks?) I can install the client version. I've been looking through KB 970148 to confirm this understanding but don't see anything. Have you installed the server and client rollups and different times? You can install rollups in any order. We have lots of workstation without any rollup and different rollup levels all around the world even server is updated already to Rollup6 level. br Jukka M "Avogadro" wrote: > It's my understanding that I can install Rollup 6 server, then later...

hiding time
I need to hide the time portion of a datetime field on a form in CRM. I do not want to change the field to a date only field. How can I do this? First of all, setting the format of a field to Date Only does not affect the storage; it will still store DateTime values. The only effect this has is to hide the time portion on the form, and in views, so this might be just what you want. Alternatively, the following code in the Form_Load event of your form: crmForm.all.mydatefield.ShowTime = false; where 'mydatefield' is the schema name of your date field. Note the ShowTime propert...

Time Formulas #2
Hi,hope you can help I have today's date displayed in cell U3 using the =TODAY() formula. In cell AD2 i would like the cell to show today's date up to 10:00p and after 10:00pm to show the next workday using the formul =WORKDAY(U3,1) Thanks for your hel -- Message posted from http://www.ExcelForum.com Hi ADE2 Try: =IF(TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))<--"22:00:00",TODAY(),WORKDAY(TODAY(),1)) I've made it independent of U3. the -- before "22:00:00" coerces the string text to the time serial number for that time string. -- Regards Norman H...

seperating time
Hello, is there a function I can use that will seperate time from one cel into 2 cells. Example: Column A is a list of times (military), A =(13:37) I would like B1 to display 13 and C1 to display 37. Thank -- Message posted from http://www.ExcelForum.com Hi, In cell B1, =HOUR(A1) In cell C1, =MINUTE(A1) Hope this helps! In article <Oreg.16xvp0@excelforum-nospam.com>, Oreg <<Oreg.16xvp0@excelforum-nospam.com>> wrote: > Hello, > > is there a function I can use that will seperate time from one cell > into 2 cells. Example: Column A is a list of ti...

CRM 4.0 Attachment Time stamp
Hi, We have found that at a Client site and also on the Microsoft CRM 4.0 VPC from Microsoft that when you add an attachment it puts the time in as GMT rather than the local system time of the user. Any ideas on how to resolve this? Thanks ...

Date and time as long format in the taskbar
Operating system Windows 7 professional 64 bit the time is shown in the desktop of Windows in the taskbar down on the right. Is there the possibility in this place of showing the time and date into long format, example: Su, 29.08.2010 13:34:47? thanks Josef Josef, I don't think so, but if you "hover" your mouse cursor over the date shown, it will display "Sunday, August 29, 2010" (American format). The format displayed is controlled by the "Long Date" format chosen in "Control Panel/Regions and Languages". Will those choices sa...

"Manual calculation" takes less time. Why?
An excel workbook (approx 10 MB) compiled from a 3rd party program used to take 30 minutes to save. After choosing "manual calculation before saving" saving time was reduced to 3 minutes. Curious to know why... Thanks in advance! Kjetil If you just force a recalculation (without the save), how long does that take? (I don't have a guess when you answer it's not close to 30 minutes!) Kjetil wrote: > > An excel workbook (approx 10 MB) compiled from a 3rd party program used to > take 30 minutes to save. > > After choosing "manual calculation befo...

SFO Client Record Times Change on Sync to CRM Server
When I am on a client site, I open up an account record on SFO Client and enter in all of the details of what is occurring while I'm on site. Sometimes this information is important according to the time it was completed. I rely quite heavily on the time stamp made by the SFO client. When I come back to the office and then resync, the time changes (thus disregarding my important information). I don't understand why this would be by design. Please add this to a service pack or a patch. This feature is very important for me and clients I am considering this for. ------------...

Two lines with a different x-series (please help)
I would greatly appriciate any help I could get on this I am trying two plot two different lines on the same chart line 1 Line 2 X axis Y axis X axis Y axi 0 1.32 0 1.3 8.83 21.22 12.5 23.5 I have no problem with the chart accepting the new Y values (as a different series) but as you probably know you can only have 1 catagory x label so my chart does not show the second line extending out to 12.5 it only extends to 8.83 on a different slope and stops. How do I get it to accept the addittional x value and still have to seperate lines? Thank yo Select the data for the second line Choo...

Negative Time Calculation
I have the following formula that calculates the difference between an employees clocked time and their amended times if manual interventations have been made =IF(K63="","",IF(AND(K63>0,K60=""),(K63-K59),(K60-K63))) <the cell is formatted as [h]:mm> The formula returns a ##### value when the above value is negative, however I want to see the negative hours shown as -2:32 etc Is this possible Thanks I believe that changing my base date to 1904 works, but that can cause serious grief Any other work around "John" <john@yahoooo...

Resize multiple objects at one time
How can I resize multiple objects at one time and maintain proportions between all of the objects? Thank you. Select all ctrl+A, group (ctrl+shift+G). This won't work with text boxes, but you can select an option in AutoFit. (Edit menu). -- Mary Sauer http://msauer.mvps.org/ "MasonCross" <MasonCross@discussions.microsoft.com> wrote in message news:0054DCC1-0D91-4823-84C2-75BDAE62CEAC@microsoft.com... > How can I resize multiple objects at one time and maintain proportions > between all of the objects? Thank you. Mary Sauer wrote: > Select all ctrl+A, gro...

Outlook sends same email multiple times
Outlook 2002 has just started to send email with attachments a number of times eventhough I only checked send once - any ideas? ...

Run-time error 40002
I am doing a new install of RMS 2.0 and when I try to run the Department Cost/Sales(Tax-Inclusive) Sales Report I get the 40002 error and the Manager shuts down. Can anyone tell me why this is happening. Thanks! hi Tmac, can you send the content of below mentioned file store at C:\Program Files\Microsoft Retail Management System\Store Operations\Reports\Sales - Department Sales (Tax-Inclusive).qrp or what is the detail of error . This error seems that you have connection problem but if you are only having this problem in the above report then we have to look at the contents, have you...

Getting exception "The node to be inserted is from a different document context."
Hi, I've got the following piece of code that's causing me a bit of a problem XmlDocument doc = new XmlDocument (); XmlNode rootNode = doc.CreateNode (XmlNodeType.Element, "usersettings", null); User user = new User (userid); XmlNode rolesNode = user.GetRoles (); rootNode.AppendChild (rolesNode); : : The last line (rootNode.AppendChild (rolesNode)) throws an exception "The node to be inserted is from a different document context." How can I work around this? The code above is from a Web-service that collects various infor...

Project Time/Expenses take allow segment overrides from employee c
You should be able to pull specific segments from the employee card for gl reporting. Now if you have 3 employees that do development and they are in different department you have to have 3 development cost categories if you want it to post to a different gl. Or in my customers scenario they have 16 departments and they all file employee expenses. In order to track airfare by dept I have to have 16 cost categories for airfare. I should just be able to have a setting to pull that segment from the employee card. ---------------- This post is a suggestion for Microsoft, and Microsoft r...

Showing BCC field all the time in OWA using Exchange Server 2007
I've seen this ? posted but no answers due to poster not providing enough info about version, etc. I have to use BCC a lot and I work remotely so I use the companys Outlook Web Access App to access my email. It is painful to have to go through the process each time to get the BCC field to display or to use the Address book. Is there any setting either indiviually (preferred) or in Exchange to be able to have the BCC field shown by default instead of having to turn it on each time? Thanks in advance. No. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the...

set up different default printer for MS Excel
I have a printer A set up as default for my PC. I want to have a printer B to be default one for ALL NEW Excel files. So task is not to change it manually or via some macro button; the task is to have the printer B ready directly after opening of Excel file. Any suggestions? Stefan, You can change the default printer for all applications with the Windows Control Panel. Start - Control Panel - Printers and Faxes. Right-click the printer you wish to be the default, and choose "Set as Default." To set a default printer for Excel only (different from the Windows default printer...

Help identify column differences
Hi I have a spreadshheet with 2 of the columns being "Interest Paid" & "Interest Due". I need to add a formula that will show me where the two columns have different values or the same values. For instance, if Interest due is 400 & interest paid is 400, I would like the cell next to it to say, "DELETE". If however the Interest Due is 400 and Interest Paid is any other amount other than 400, I would like the cell next to it to say "KEEP" and, if possible, formatted to be RED. Many thanks Malcolm Davidson See this webpage Malcolm ht...

Time Problem #3
I've got an excel spreadsheet that tracks attendance (time in/out). We work 8 or 10 hour days depending on the shift. We are required to be as close to 0 hrs over/under at the end of each month however we sometimes carry a balance of x amount of minutes. My goal is to create a cell that you can input your time over/under into which represents any overage/underage from the previous month. This cell will then add into the cumulative +/- total for the current month so that you know when to clock out for the day. My Cumulative +/- cell has this formula =SUM($F$16:F$46+D8) Where column ...