adding hours and minutes ignoring seconds

I have a column of cells which displays the results of a formula i
hours and minutes (no seconds). For instance A1 shows 3:04 and A2 show
0:18. When I sum these two cells I get 3.23 instead of 3:22. Excel seem
to be counting the seconds too, which results in the additional minut
being added. 
How can I make excel add only the hours and minutes without th
seconds?
Thanks - cmiller@keithandschnars.co

--
cb
-----------------------------------------------------------------------
cbm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2824
View this thread: http://www.excelforum.com/showthread.php?threadid=47801

0
10/20/2005 7:49:05 PM
excel 39879 articles. 2 followers. Follow

4 Replies
355 Views

Similar Articles

[PageSpeed] 38

On Thu, 20 Oct 2005 14:49:05 -0500, cbm
<cbm.1x7via_1129838701.8377@excelforum-nospam.com> wrote:

>
>I have a column of cells which displays the results of a formula in
>hours and minutes (no seconds). For instance A1 shows 3:04 and A2 shows
>0:18. When I sum these two cells I get 3.23 instead of 3:22. Excel seems
>to be counting the seconds too, which results in the additional minute
>being added. 
>How can I make excel add only the hours and minutes without the
>seconds?
>Thanks - cmiller@keithandschnars.com


Perhaps:

=SUMPRODUCT(ROUNDDOWN(rng/TIME(,1,),0)*TIME(,1,))

where rng is the cell reference containing the times you wish to add.  Format
the results as [h]:mm


--ron
0
ronrosenfeld (3122)
10/20/2005 8:23:28 PM
cbm,

Try as I might, I could not get the same results as you.  My addition
of 3:04 and 0:18 always yielded 3:22.  Is there something fishy in the
manner in which your cells are formatted?

0
glkehler (10)
10/21/2005 2:32:52 AM
Ron Rosenfeld Wrote:
> On Thu, 20 Oct 2005 14:49:05 -0500, cbm
> <cbm.1x7via_1129838701.8377@excelforum-nospam.com> wrote:
> 
> >
> >I have a column of cells which displays the results of a formula in
> >hours and minutes (no seconds). For instance A1 shows 3:04 and A
> shows
> >0:18. When I sum these two cells I get 3.23 instead of 3:22. Exce
> seems
> >to be counting the seconds too, which results in the additiona
> minute
> >being added.
> >How can I make excel add only the hours and minutes without the
> >seconds?
> >Thanks - cmiller@keithandschnars.com
> 
> 
> Perhaps:
> 
> =SUMPRODUCT(ROUNDDOWN(rng/TIME(,1,),0)*TIME(,1,))
> 
> where rng is the cell reference containing the times you wish to add.
> Format
> the results as [h]:mm
> 
> 
> --ron

Thanks Ron, Barry Houdini at mrexcel modified my formula with the roun
function as follows and it works fine. The results are shown in a colum
as h:mm and when I sum the column I get the correct hours and minutes
=IF(F16="SHRUB",ROUND(1.5/(J16*0.75)/3,0),0)/144

--
cb
-----------------------------------------------------------------------
cbm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2824
View this thread: http://www.excelforum.com/showthread.php?threadid=47801

0
10/21/2005 3:02:56 PM
Haukwa Wrote:
> cbm,
> 
> Try as I might, I could not get the same results as you.  My addition
> of 3:04 and 0:18 always yielded 3:22.  Is there something fishy in the
> manner in which your cells are formatted?

Thanks for writing - The times shown are the result of a formula an
when I added them I got the incorrect 3:23. Excel kept track of th
seconds even though I had formatted to show only h:mm. Using the roun
function has solved the problem

--
cb
-----------------------------------------------------------------------
cbm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2824
View this thread: http://www.excelforum.com/showthread.php?threadid=47801

0
10/21/2005 3:19:24 PM
Reply:

Similar Artilces:

v 2007 Adding Color to Outlook.hol
I would like to add several different types of dates to my Outlook.hol ~ and have them show up in different colors. Ex. school days off in red, payroll in yellow, etc. Is there a way to put hex color codes into the Outlook.hol file? Or in another file? Thanks!! -- SteffiMom School Media Specialist "SteffiMom" <SteffiMom@discussions.microsoft.com> wrote in message news:86ED46A2-DC51-40C3-853A-04FEBE3C4FAA@microsoft.com... >I would like to add several different types of dates to my Outlook.hol ~ and > have them show up in different colors. Ex. schoo...

Counting hours mins secs between 2 dates and times
Hello In my worksheet I have 4 columns. I have a start dates and end dates as well as times for start and end. I need to calculate the time elapsed between the start date and time till the end date and time. But it must display it as HH, MM, SS. I must show whether it has gone over 100 hours. That is the urgent level of my time frame I need it to look something like this Start Date Start Time End Date End Time Elapsed Time 06/01/2004 7:00 AM 6/08/2004 7:00 PM 144:55:24 I keep coming up with 00's Thanks Bob Use =(C2+D2)-(A2+B2) , but you'll need ...

Adding Fields + Technical Documentation
Hello, I was wondering if there is a possibility of us to include extra fields in the database in order to be used to represent "Status" in orders. I have checked the db and it seems like there is a table called StringMap which holds all the fields information. I have seen for example there are 3 Values for statuses for orders i.e. 0 - Active, 1 - Submitted and 2 - Cancelled. What if I wanted to Put an extra one 3 for Closed Order? And what effect would this bring on reporting? Or is there any way I can get hold of some technical documentation regarding the db structure and what e...

adding terms to speling dictionaries in Live Mail
There are many family names and other terms that are being marked as errors in Live Mail spelling dictionaries. There seems to be no obvious way to add these terms to the dictionaries. Any suggestions? Avraham Hi, Avraham. "Avraham Hanadari" <avhanadari@012.net.il> wrote in message news:O7S1ozMeKHA.1156@TK2MSFTNGP05.phx.gbl... > > There are many family names and other terms that are being marked as > errors in Live Mail spelling dictionaries. There seems to be no obvious > way to add these terms to the dictionaries. Any suggestions? > Avra...

Run code every 15 minutes
Hello Is there a way to run a set of codes every 15 mins. eg. Private Sub FormButton_Click() Do something here wait 15 mins and then repeat the Do something here end sub Thank you At the opening of your database, open a form in hidden mode and use the timer event with a 15 mintue loop. But I have to ask, what need to be run every 15 minutes? -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Karren Lorr" wr...

Outlook adding a extra "!" in message body
I am generating email from Visual Fox Pro using Outlook 2003, but when the emails are recieved sometimes it adds an extra "!" followed by an extra line break in the message body 2 or 3 times. It happens sometimes only. Can anyone tell what could be the problem? -- shivam It is probably your FoxPro code. Outlook does not do this natively. = Best to ask in a foxpro group to see what is happening. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. How to ask a question: http://support.microsoft.com/KB/555375 After furious head s...

Adding Front-End to Existing Environment
My company has been hosting Exchange services for some time now to area small businesses. So far we have been successful by doing simple upgrades to our one Exchange 2000 server to accomodate our small, but expanding, client base. We are now in a position where our single server is almost to the point of being very inefficient so I have purchased a new server to act as a front-end. My questions: 1) Do I need to run forest/domain prep again, or just install Exchange 2000? 2) Since I have never really worked with a front-end/back-end topology, are there any additional settings or decommis...

Adding more details to Receipt
Hi.. I want to add some more details to the receipt, the details are generated dynamically... Can I add the details through VB code ?? Can you please tell me how ?? Vicky... :-) Session.Variable1 thru 10 can pass values to the receipt. Rob "Vicky" <Vicky@discussions.microsoft.com> wrote in message news:4D006F55-6F5B-4821-A8E4-D1915036E194@microsoft.com... > Hi.. > > I want to add some more details to the receipt, the details are generated > dynamically... Can I add the details through VB code ?? > > Can you please tell me how ?? > > > Vick...

adding times #3
I have a timesheet and I need a formula that will ADD the following times: 1.67 1.11 The natural result is: 2.78 But I need : 3.18 (Need the minutes to convert over. 60 minutes = 1 hour) I dont to have to manually add everthing over .60 Any ideas? Thanx J.W., you are giving yourself a lot of trouble by trying to work with decimal times. It is much better to use XL times like 1:40 1:07 (note that the hours and minutes are separated by a full colon) You can then simply add the times like A1+A2 or SUM(A1:A2) If your total is liable to exceed 24 hours then custom format the cel...

Staff Working Hours? (Booking System)
Hi. Im currently doing a database for a Salon Booking System and was wondering if you could help with some explanation on something. I want to have the staff available certain times on certain days depending on what they can work so they all have different shifts. How would i implement this into a form so i can enter that information? Also how would i go out about searching whether a staff member is working that day/time to be able to take that appointment along with whether they can actually do that treatment or not. Thanks for any help you could give. Jake, as I said, this is a huge que...

Keeping track of and totaling hours worked
I keep track of my daily work hours using Excel. I enter starting time in one column and ending time in the next. I would like for the next column to calculate the total hours worked and rounded to the next 1/10th of an hour. I know it can be done, but I haven�t figured it out yet. Any help would be very much appreciated. Popeye -- Popeye ------------------------------------------------------------------------ Popeye's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30454 View this thread: http://www.excelforum.com/showthread.php?threadid=501284 Hi Popeye 1/1...

Adding GL account
I am new to Great Plains. Does anyone have documentation for adding a new account to the general ledger? Try Cards->Financial->Account off the menu. Then if you need more help, click on the little question mark icon on the Account Card - the help link will give you many gory details about every field and button on the card. "Andrea" wrote: > I am new to Great Plains. Does anyone have documentation for adding a new > account to the general ledger? ...

extension added
In Outlook new mail, I click on insert file. When file is added the extension is duplicated. Example: I inserted name.doc and file name was changed to name.doc.doc This happens to systems at location A but does not happen to systems at location B At location B we also send ZIP files by renaming the file junk.zip to junk.tzipt Loation B can insert and send with no problem. The users at the other end rename the file back to zip and unzip it to get the contents. At location A, clicks on insert file, highlights junk.tzipt and they get junk.tzipt.zip what is going on at location A ...

Slave HDD added from dead PC
WinXP ver 5.1 2600 SP3 Hi, I have taken the HDD from our old "dead" PC, both are on XP home. It seems to be working okay, I can see the contents etc. The old computer had many games on it...will these be able to be played on the new PC as if they were still in the old PC. When I look in My computer - I can see on the screen the two folders that were there before One named Shared documents..........one named Brian's documents Under here is - hard disk drives local disk (c:) - this is in fact the "new" slave HDD (40gb with 25gb used) and local...

Retrieve dnsRecord entry in AD with ActiveDirectory PSDrive?
How does one retrieve the dnsRecord attribute from a Dns-Node objectCategory in Active Directory using the ActiveDirectory PSProvider on version 2.0 PowerShell with ADWS installed on the AD server and an "AD:" PSDrive configured? "Get-Item 'AD:\DC=a,DC=b.com,CN=MicrosoftDNS,CN=System,DC=b,DC=com' | Get-Member" does not appear to return a method for listing entries. TIA Hey, The MS AD provider does not expose that property I'm afraid, nor does it expose a way to get arbitrary properties it seems. That leaves converting the ADObject ...

after adding new address they do not come up in address book
I have a very large address book and now when I add new names to it and want to send out my newsletter the addresses I have added do not come up in the list. is there a max to the number of conacts in my list? any suggestions on how to fix this problem? Please provide at least some information. Outlook does not even use an address book. Are you certain you are talking about Outlook and not some other program? What version of Outlook is this? How are you trying to add entries? What happens when you try? -- Russ Valentine [MVP-Outlook] "ginaweston" <anonymous@discussions.micr...

Adding or removing rows
Hi all. I have got a macro that I use for adding and deleting rows while sheet is protected (Gord Dibben helped me with, and is working well). I do have one thing that may be a problem and that is there are certain rows I dont want to be changed. These are the last row of each section and have the sub totals. The problem is that if one of these rows get deleted by misstake it will upset the final total at end of the sheet. Is there anyway to stop these from being upset and protected. Regards Chris Post your code... -- HTH... Jim Thomlinson "Chris" wrote...

Visio 2000 deployment AD
hello, I have a problem with the deployment of Visio 2000 in an AD environmenr via GPOs. i tried adding all MSIs to the GPO User object, the EventLog says "yess, worked", but neither the Visio Icon in start menu nor program files are installed. I installed all msi files, but apparently am missing something crucial. There are about 30 to 40 MSIs in our Enterprise edition visio. yours wulff ...

Adding Disclaimer to mailboxes
Hi folx; How do I add Disclaimer text to every outgoing email at Exchange server level. or is it possible? I have exchange 2k3 standard. Thanks for your help chet Hi, You can try EventSync registration like in article http://support.microsoft.com/default.aspx?scid=kb;EN-US;317680 I did in Exch2000 and worked. Try in lab first. Cesar "Chet Guzel" wrote: > Hi folx; > How do I add Disclaimer text to every outgoing email at Exchange server > level. or is it possible? I have exchange 2k3 standard. > > Thanks for your help > chet > > > Hi Chet, ...

Can someone tell me if this is possible
Hi, Where I work we get a lot of users requiring access to shared mailbox's. As a result I was wondering if there is anyway that I can send an email to the users requiring the shared mailbox added to their outlook profile with a link to a script/batch file/form that will add the mailbox as an additional one under their profile. I've searched both these groups and the web and this looks like it may be possible but I just cannot get a concrete answer. Thanks for any help in this matter. GC You can probably add a mailbox using a batch file but remember, Outlook will bar access ...

adding data table with different data to a graph
I have created a graph and I know how to add a data table to the bottom that has the data in the graph. However, I wish to put different data in that table. Is there a way to do that? You can similate the effect. See Custom Chart Table http://www.tushar-mehta.com/excel/newsgroups/data_table/index.htm -- Regards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 2000-2005 In article <4DC8A259-AB15-4AB5-8805-03AE6D173CD1@microsoft.com>, ratio@discussions.micros...

adding conflict between two users
I have DB(Access 97) where multi- user work in a network.Today, I encountered this problem.Two users were in it and do edition and adding a new data on the sameform at the same time.User 1 was adding data on the form (just say form A) and user 2 wasadding data as well on the same form(A) at the same time.The problem is that user 2 could see data entered by user 1 on thatform(A) at that moment.As I know, access handles problems like this by itself. Ex) provide awarning...Or something I have to check VB code?My question?Are there Adding conflicts in ACCESS 97 (windows XP) between multi-user in ...

Mixed mode entries in AD when we are in Native mode
Hello, When we look at the properties of our organisation it lists us as being in Native mode and we are using native mode features (Query based distrub. lists etc..) however in ADSI edit these keys are still present with data: • msExchEncryptedPassword • msExchLegacyAccount • msExchLegacyDomain and msExchAdminGroupMode is set to 2. We are thinking about following the article http://www.microsoft.com/technet/prodtechnol/exchange/Analyzer/81840f36-8942-4d64-87b1-1cb4a631c366.mspx?mfr=true to delete and reset these entries. Are there any risks associated with this procedure that any ...

deleting second calendar in outlook 2007
Hi, i inadvertently created a new calendar (i say "inadvertently" as i didn't do it volountarily). i am not able to sync my PDA any longer to outlook calendar, and I cannot get rid of the second calendar, the one created this morning, which is empty. is there a way to do it? if not, how can I manage the way my PDA syncs again with the first calendar? i saw several posts around with a similar question, but no response seems to be working for me What type of PDA? I know iphone's (possibly anything that uses itunes to sync) can create new default calendars if not ...

AD Users and Exchange Mail account
Does anyone have a script or tool that they would recommend that allows you to query AD and retrieve the associated email address for that user? "-->AP<--" <ap@mail.com> wrote: >Does anyone have a script or tool that they would recommend that allows you >to query AD and retrieve the associated email address for that user? Just the mail address? You don't need a script, just the Active Directory users and Computers snap-in. Make sure the e-mail address is a colimn displayed in the right-hand pane. Then select the OU in the left pane, right-click it and p...