how do I calculate times and get as a result numbers

I got a Spreedsheet that I whant to use as a time sheet, this is so far what 
I got done wrong         what I want to get is  right
a1= 10:00am    time in                   a1= 10:00am
b1=  2:00pm     time out (lunch)      b1=  2:00pm
c1=  2:30pm     time in  (lunch)       c1=  2:30pm
d1=  8:00pm     time out                 d1=  8:00pm
e1=  8:30          total                      e1=   8.5

the answer has to be  a number if the time is #:0<= #:14  = #.0
                                                                     
#:15<=#:29  = #.25
                                                                     
#:30<=#:44 = #.50
                                                                     
#:45<=#:59 = #.75
I'm using Excel 2002
0
10/23/2004 7:21:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
330 Views

Similar Articles

[PageSpeed] 57

Hi
in E1 enter the formula
=(B1-A1+D1-C1)*24
and format this cell as number

--
Regards
Frank Kabel
Frankfurt, Germany

"IgnacioLeon" <IgnacioLeon@discussions.microsoft.com> schrieb im
Newsbeitrag news:717FC15D-389F-467F-A58A-E3EC9CE468A4@microsoft.com...
> I got a Spreedsheet that I whant to use as a time sheet, this is so
far what
> I got done wrong         what I want to get is  right
> a1= 10:00am    time in                   a1= 10:00am
> b1=  2:00pm     time out (lunch)      b1=  2:00pm
> c1=  2:30pm     time in  (lunch)       c1=  2:30pm
> d1=  8:00pm     time out                 d1=  8:00pm
> e1=  8:30          total                      e1=   8.5
>
> the answer has to be  a number if the time is #:0<= #:14  = #.0
>
> #:15<=#:29  = #.25
>
> #:30<=#:44 = #.50
>
> #:45<=#:59 = #.75
> I'm using Excel 2002

0
frank.kabel (11126)
10/23/2004 9:09:19 PM
Reply:

Similar Artilces:

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

I cannot get the pictures to show on screen in Publisher web previ
I have tried embedding and linked but nothing seems to work. Do I have to apply a master page? It is to be a one page web site. This is probably so basic but I am trying to teach myself how to use it. What do I have to do to have them show in preview and once uploaded? Thank you. Are you using FireFox to preview or IE? DavidF "rendul" <rendul@discussions.microsoft.com> wrote in message news:C9B0A640-6C87-4E9A-8A0E-5F32F7DAC0DE@microsoft.com... >I have tried embedding and linked but nothing seems to work. Do I have to > apply a master page? It is to be a one ...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Roman and Arabic numbers in SEQ
Running 2003 on XP. Got a doc that has SEQ numbering. The code is: ARTICLE{seq level0\h\r0}{seq level1\h\r0} etc. to level 7 and the last entry is {seq level0\r1\*ROMAN) This displays ARTICLE I The next level down is has this code: {seq level0\c\*ROMAN}.{seq level1\r1 \*arabic} This displays as : I.1, with numbers until article five displaying as V.1, V.2, etc. I want it to display as 1.1, 1.2, etc. I tried replacing the ROMAN with arabic, but it didn't change anything. I have spent hours trying to figure this out. Using reveal formating and styles, they are no styles...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Time Clock Systems
Does anyone have a recommendation for a time clock system that integrates well with GP? On Oct 5, 10:20 am, kcd <k...@discussions.microsoft.com> wrote: > Does anyone have a recommendation for a time clock system that integrates > well with GP? We just implemented Time Matrix by Business Computers (www.business- computers.com) and are very happy with it. We implemented quickly the hardware wasn't propietary or complicated so we were able to source our own stuff. Troy I can speak highly of Unitime's time and attendance system. They are a relatively low cost solution t...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Getting rid of selection
How can I get rid of the selection rectangle? It seems that it's always there, with a heavy black rectangle, or there's a light black rectangle marking where it was. I'm trying to get rid of it altogether, so I can capture an image of the sheet for use in a webpage. I can achieve the effect that I want by selecting a cell which is outside the area that I'm trying to capture, but now that I've found that I cannot get rid of it entirely, it is driving me nuts trying to do so. -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk You could al...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Workplace Queues
We just rolled out CRM a few weeks ago. I'm getting a lot of complaints from the users about the thousands of items showing up in their My Work\Queues\In Progress folder. When I look at my own items, I have about 1000 activities showing in my In Progress folder but when I open them up most of them are owned by someone else. According to the Help description of this folder, only items that I have accepted should show up in my In Progress folder. I've never accepted anything, so I'm not sure anything whatsoever would be showing up in this folder. We used Scribe to import ...

Calculating totals
I have two columns on a worksheet: "Project Codes" and "Totals." I would like to have columns on another worksheet that will automatically total up the different project numbers "A,B,C,etc." How do I do that? Thanks in advance for your help, Technically Handicapped Enter a *unique* list of your "Project Codes", starting in A2 of Sheet2. In B2, enter this formula: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) And copy down as needed. -- HTH, RD ===================================================== Please keep all correspondence within the G...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

Get info from Fidelity
I use money's portfolio manager to retrieve info from my Fidelity accounts. However, it only retrieve stock and bond informations. It did not retrieve the money market fund information. How can I make it download money market fund info? In microsoft.public.money, Peter Tso wrote: >I use money's portfolio manager to retrieve info from my >Fidelity accounts. However, it only retrieve stock and >bond informations. It did not retrieve the money market >fund information. How can I make it download money market >fund info? Fidelity does things a differently th...

Problem displaying expected results with CString
I am writing a MFC program to import data from a single table database into a normalized database with numerous tables. The first component opens a recordset object to the database and performs some basic tests on the field value, and the plan is to write the records out to a spreadsheet that fail to meet any of the criteria defined for the field. Along with writing out the record, I want to populate a comments field describing what failed. I have tried to implement this with a CString variable; I initialize it to a blank string each time a new record is examined, and then as each field is che...

Get Xml from web .
This is a link to an address where I would like to access xml. http://setiathome2.ssl.berkeley.edu/fcgi-bin/fcgi?cmd=user_xml&email=XXXX The XXXX is the email address of a user. All I want to do is access the xml and view it in a datagrid. I have read several tuturials and articles and have not been able to find something that would explain how to do this. Does anyone know of a sample code or something close I could use as a reference. I am using visualstudio.net. and visual basic. THX Take a look at using the following classes on your app: HttpWebRequest HttpWebResponse DataSe...

Auto calc YTD Numbers
Cells A1:L1 equal "Jan" thru "Dec". M1 equals "YTD". Cells A2:L2 equal numeric values. If you haven't guessed it already, I would like M2 to sum all the values A2:L2 for those months previous and including the current month (but not future months). What is the best way for going about this Here are 2 ways =SUM(OFFSET($A$2,,,,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0))) or =SUM(OFFSET($A$2,,,,MONTH(TODAY()))) hopefully your computer's clock is accurate -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjo...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...