How do I set up a formula on a time sheet to calculate time in 1/.

I am setting up a time spreadsheet and I want all of my totals to be 
calculated in 1/4 increments.

Example: 
IN                 OUT          IN              OUT            TOTAL
7:01 AM     11:47AM   12:50 PM    4:32 PM  =  8:30 hours
0
gschmid (1)
1/18/2005 1:07:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
490 Views

Similar Articles

[PageSpeed] 9

Don't know what you total formula is, but let us say it is

=(B2-A2)+(D2-C2)

If you want to round this to the nearest quarter hour use

=ROUND(((B2-A2)+(D2-C2))*96,0)/96

If you want to round up a quarter hour, use

=ROUNDUP(((B2-A2)+(D2-C2))*96,0)/96

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"gschmid" <gschmid@discussions.microsoft.com> wrote in message
news:77F2CF95-9B50-446B-A3D8-1591A4C862E4@microsoft.com...
> I am setting up a time spreadsheet and I want all of my totals to be
> calculated in 1/4 increments.
>
> Example:
> IN                 OUT          IN              OUT            TOTAL
> 7:01 AM     11:47AM   12:50 PM    4:32 PM  =  8:30 hours


0
bob.phillips1 (6510)
1/18/2005 1:34:52 PM
Just another way:

    =CEILING(B2-A2+D2-C2, 1/96)

In article <#2JsAKW$EHA.2804@TK2MSFTNGP15.phx.gbl>,
 "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote:

> If you want to round up a quarter hour, use
> 
> =ROUNDUP(((B2-A2)+(D2-C2))*96,0)/96
0
jemcgimpsey (6723)
1/18/2005 1:48:23 PM
Reply:

Similar Artilces:

Displaying totals of calculated fields in Pivot tables/charts
I find that, when I create a Pivot table, I can only derive totals for those items that have been entered by hand - wnen I try to get totals of calculated fields, I only get the count of occurrences of those fields Any ideas ? It sounds like your calculation columns may be returning something besides numbers. If you have formulas that look like: =if(something's_true,"",a1*3+5) Then that string value ("") will make it so that the pivottable wizard will default to count. Just double click on that "button" in the Data Area (when you're in the Wizard) and...

How do I set up a Pareto chart in Excel?
It would be useful if I could display some information in a Pareto chart. Excell dosn't seem ot offer this option. Can anyone point me in the right direction? Paul, This site described the process: http://www.isixsigma.com/offsite.asp?A=Fr&Url=http://gsep.pepperdine.edu/~fmadjidi/Stats/pareto.html ---------------------------- Regards, John Mansfield http://www.pdbook.com "Paul" wrote: > It would be useful if I could display some information in a Pareto chart. > Excell dosn't seem ot offer this option. > > Can anyone point me in the right direction? ...

Bills & Deposits time frame shown
This might be a losing question but I wish someone would make some changes to the Bills & Deposit screen reporting. I remember the old versions that when you gave it a 90 day time frame it showed ALL entries for the 90 days. The random picking & choosing of bills to show is totally annoying, I get only one deposit showing and a few near term bills and a random scattering of bills for up to 6 months down the road. I know if I go to the cash flow screen I can get a scrolling window of everything for a chosen time span but if I enter all this in on the Bills & Deposit screen WHY can...

sumif formula required
Hello I have only basic knowledge of excel and need a formula to extrac information. A B C Client Process Sqm CEK Fibre 100 RDF Gloss 200 ERF Emul 150 CEK Fibre 100 What formula should I use to extract all CEK clients who purchase th fibre process giving total sqm. I would be extremely grateful to anyone who...

microsoft outlook date and time incorrect
Please guys everyone knows about the date settings on microsoft outlook so this fault is alittle harder than that to locate. Emails while in your own computer or sent to yourself have the correct date and time. When opened in the receiving computer it has the correct date and time. But as listed unopened in the receiving computer it has the incorrect and time. date might be the previous day or the next day. Tme has no relationo time sent. This Microsoft outlook using office 2000 or office 2007 on the same computer. Any advice appreciated. It would be best to ask this...

Excel 2007 - VBA
How can I set the xaxis to an arbitrary angle, say -70 deg using vba? I recorded a macro to do this and it does not actually do anything??? Tried it 3 times. I end up with the proper layout but the recorded macro does not actually format the xaxis when run again?! QB Hi, Try, With ActiveChart.Axes(xlCategory).TickLabels .Alignment = xlCenter .Offset = 100 .ReadingOrder = xlContext .Orientation = -70 End With Cheer Andy On 24/02/2010 15:32, QB wrote: > How can I set the xaxis to an arbitrary angle, say -70 deg usi...

problems printing multiple copies on one page, only prints 1
Hi there I am trying to print four copies on one piece of A4 paper, the system and printer resizes the document and prints it in the top left hand corner, but no other appear on the same page Verify your work area is � or less the size of your paper stock. When you go to print select File Print Page Options "Print multiple copies per sheet." -- Don Vancouver, USA "Gary" <Gary@discussions.microsoft.com> wrote in message news:7412DEB1-B05A-427C-9D42-6C71E85A23F0@microsoft.com... > Hi there I am trying to print four copies on one piece of A4 pap...

aggregate calculations
I have two columns of data: Date Value 1/1/05 9 1/1/05 10 1/1/05 12 .... .... 1/9/05 16 1/9/05 4 There may be a variable number of dates, and does not contain a complete list of consecutive dates (i.e. some dates have no associated data and are not in the list) I want to produce a calculation the that subtracts the MIN for each date from the MAX of each date. I do not necessarily have to display the max and min values - I am really after the result Date (max - min) 1/1/05 3 <12-9=3> 1/9/05 12 <16-4> What is the best way to do this? I have experimen...

"Average" calculation formula needed
Hello, I have Excel 97, and am trying to learn formulas. I need to find the "average" of certain values in column B below. This is an example of my worksheet A B 1 TRUE 1.0% 2 FALSE -.8 3 TRUE -.5 4 FALSE -. 5 TRUE 1. 6 TRUE -. 7 FALSE . What I need: In Cell C50 I want the calculated result in the form of a "%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the average % (both positive and negative) in column "B" that are next to "T...

Excel Formula #8
I currently have a spreadsheet set up to record staff holidays or sickness. But i would like the column AG to total how many times the letter S appears in any of 31 cells from colums B to AF. Each cell may have either nothing entered into it or S or H. Thanks for any help Hi Sharon Try =COUNTIF(B2:AF2,"S") -- Regards Roger Govier "Sharon" <Sharon@discussions.microsoft.com> wrote in message news:399B5039-CAC0-4428-BBB9-18C2C860E564@microsoft.com... >I currently have a spreadsheet set up to record staff holidays or >sickness. > But i would like ...

Calculating Days Between Dates
I use the following formula to calculate the days between dates and to return zero when dates are the same � =IF(OR(A1="",A2=""),0,DATEDIF(A1,A2, "d")) How would I also allow for the eventuality where the date in cell A2 is earlier than A1 and where the result would then be a minus figure ? At present the formula returns a "Num" error when this happens -- martins ------------------------------------------------------------------------ martins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31616 View this thread: htt...

Lan Settings
my dsl is slow BellSouth told me to go to Local Lan Settings and make sure not boxes where checked then go to General Tab and delete files button under the Temporary Internet Files. I can't seem to find this either. Can you help me. Thank You Brenda I think you are in the wrong newsgroup. This one has to do with the program Microsoft Publisher, for desk-top publishing. You want a forum that deals with Internet Explorer. On Thu, 16 Sep 2004 23:13:03 -0700, Brenda <Brenda@discussions.microsoft.com> wrote: >my dsl is slow BellSouth told me to go to Local Lan Settings and make s...

Font setting
My Outlook 2007 starts with the Inbox split into 3 columns - Mail, Inbox and the message preview. What setting determines the font and font size of the Mail column showing Favorite Folders and all the Mail Folders? Until today this was a fairly large size font but for some reason this has no gone down to a very small font. All other columns display as before. JP "CDC" <cdc@nomail.com> wrote in message news:040F1E44-3170-422C-85C3-E4A069A07379@microsoft.com... > My Outlook 2007 starts with the Inbox split into 3 columns - Mail, Inbox and > the mess...

two servers shut down at the same time?
I have two new HP ML 350 servers, one has been up a couple days, the other has been running for a couple months. Yesterday, they both went down at 2:40 PM, within 20 seconds of each other. It looks like a power failure, but they are in different rooms, each with it's own APC SC1500 UPS. There was no power interruption at the time, machines not on UPS never went down. Log files only say unexpedted shutdown possibly due to crash or power failure. What in the world could have caused that? Do I have two defective UPS's? "dlw" <dlw@discussions.microsoft....

adding multiple contacts at one time from a received email
Is there a way to add multiple contacts from the header of an email sent to me as part of a distribution group? I know I can right-click on them one-at-a-time and do "Add to contacts..." but that's a nuisance for 50+ addresses. Is there a way to do this in bulk? for a reply. thanks. Liz I don't think so. -- Judy Gleeson MVP Outlook Outlook trainer and author of Productiv_IT with Outlook www.acorntraining.com.au <lizzoob@gmail.com> wrote in message news:1162228944.601011.176680@e3g2000cwe.googlegroups.com... > Is there a way to add multiple contacts from t...

Why might some 'times' be sub-type 5 and some be sub-type 8?
Anyone have a possible explaination as to why I seem to be getting an inconsistent format on a column of times. I am downloading data from a active server web page that gives me data in excel format. The data I get is various columns of numbers , text and what looks like some times. The problem is that in my time column some of the rows are variable sub-type 5 (vbDouble) and some are variable sub-type 8 (vbString). This is producing problems for my code which is expecting all vbString type data. In general might someone think of a reason why some times come out type 5 and some ty...

Consolidating Sheets into 1 new one.
I have a workbook with 52 worksheets, each following the same format. (The sheet lists salespeople by name in collumn A, and statistics for each person across the next columns B - F.) I want to bring all the data for each of the weekly sheets and consolidate it into the same format on a summary sheet. Here's the problem: I have to deal with employee turnover. The first weekly sheet might have in column A starting with A1 Steve, Tom, Mike, and Joe in A4. The second weekly sheet might have (starting in A1) Steve, Tom, Joe, Jim, and Mary in A5. Week 3 might just have Joe, Jim, and Mary....

Change CDialog class at run-time
Is possible to change the class of a MFC dialog that uses the CDialog class to a different custom sub-class derived from the CDialog class at run-time, if so how do I do it?. TIA Todd "Todd R. Jacobs" <NO.SPAM.tjacobs@NO.SPAM.gfnet.com> wrote in message news:ucRi8S33DHA.556@TK2MSFTNGP11.phx.gbl... > Is possible to change the class of a MFC dialog that uses the CDialog class > to a different custom sub-class derived from the CDialog class at run-time, > if so how do I do it?. > > > > TIA > > Todd > > > Todd, You could use SDI AppW...

Synchronization of Tasks and Phone calls
Hi I have a script on my tasks and phone calls which sets the due date to current date and time. Whenever I create a task in CRM and synchronize it with outlook it always sets the due time to be 5:00 PM on the day instead of the current date time as per my script in CRM. Can someone please help me with why this might be happening. Thanks heaps in advance CRMUser2008 It sounds like your script might only be setting the date portion of the date/time field, and so the CRM assumes you mean 12:00 midnight (default if no time is provided), and then depending on your time zone that time is ...

VLOOKUP formula #4
I have a workbook with 2 sheets - sheet 1 and sheet 2. I have a blank column in sheet 1 (B), which I want to fill with info from column B in sheet 2, but ONLY is the info in column A in sheet 2 matches with info in column C in sheet 1. I think I should be using VLOOKUP and had a go with the following formula =IF('A20'!A2=('Course Notes'!C5),VLOOKUP('A20'!A2,'A20'!A1:D4467,2)) But it didn't work. Can someone please help or point me in the right direction? Hi try =IF(ISNA(MATCH(A20,'A20'!C2:C4467,0)),"no match",INDEX('A20'!B2:B...

Outlook times out when downloading messages
I use Outlook Express 6.0 to download my e-mails. The McAffee has been detecting the netsky.virus and cleaning it from the e-mail. However, only half of my e-mails are delivered and then the following error message occurs Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Account: 'pop.charter.net', Server: 'pop.charter.net', Protocol: POP3, Port: 110, Secure(SSL): No, Error Number: 0x800CCC0 This happens every time..aaaaaaaaaaaarrrrrrrrrrrrrrgggggggggggggghhhhhhhhhhhh ...

Slow attachment retrieval time (Exchange 5.5)
I'm having a random problems with several of my users experiencing slow retrieval time when opening attachments through Outlook 2002 sp2 (30 sec to a min). File sizes are small. Server side is Exchange 5.5 sp4 OS win2k sp4. Band width is not an issue as we have multiple T1's and DS3's connecting centers. There is no real commonality w/ 3rd party applications on users who are having issues. Used perfmon to track things like CPU , network traffic and physical disk. The only real bottle neck I see is with Read Write times being extremely high and often. Server has 851 mail accounts w...

FORMULA HELP! Find all values in column at random rows and perform calcutlation.
ex. A 1 2 3 3.9% 4 5 6 -2.7% 7 8 9 6.5% 10 11 12 1.2% .. .. .. 1000 Total of formula here. I don't know what row a % number will show up on col A but it is before row 1000. I would like row 1000 under column A to have a formula that says look for any row in column A and if you find a number, add 1 to that number and then find the next one and add 1 to that number and then multiply those two together and then find the next one add 1 then multiply to the last set...etc. Bascially, A100 would have the formula (1+A3)*(1+A6)*(1+A9)*(1+A12)...e...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Break Even, Time to Value, IRR, What do all these things mean?!?
I'm having trouble searching for this, because I am not sure what to call it. What I have is two different solutions that will cost me money. One of them has a higher cost up front, but a lower monthly cost, and the other has a lower cost up front, but higher monthly cost. What I'm interested in knowing is what the break even point is. Where do they intersect. If I choose the initially more costly solution, how many months (days, years, whatever) must I use it before it is a better overall choice over the other? For example. Solution A costs $60/month, and $368 up front. Soluti...