Subtracting time values

I am trying to calculate the difference between two cells which ar
formatted with Custom [h]:mm option. It has so far worked fine unti
now, the higher of the two values is over 10000:00. I now get an erro
stating that the higher value is not greater than the lower value. 
think it is because time values are represented by a date time syste
which perhaps cannot work above values of 10000:00

--
fishphon
-----------------------------------------------------------------------
fishphone's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2462
View this thread: http://www.excelforum.com/showthread.php?threadid=38211

0
6/24/2005 9:17:03 PM
excel 39879 articles. 2 followers. Follow

2 Replies
584 Views

Similar Articles

[PageSpeed] 10

Works for me, 1234:00 minus 12:00 gave 1222:00.

Are you the same guy who couldn't add times exceeding 1000 hours recently?

-- 

HTH

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


"fishphone" <fishphone.1r5ieb_1119650701.3665@excelforum-nospam.com> wrote
in message news:fishphone.1r5ieb_1119650701.3665@excelforum-nospam.com...
>
> I am trying to calculate the difference between two cells which are
> formatted with Custom [h]:mm option. It has so far worked fine until
> now, the higher of the two values is over 10000:00. I now get an error
> stating that the higher value is not greater than the lower value. I
> think it is because time values are represented by a date time system
> which perhaps cannot work above values of 10000:00?
>
>
> -- 
> fishphone
> ------------------------------------------------------------------------
> fishphone's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24622
> View this thread: http://www.excelforum.com/showthread.php?threadid=382119
>


0
bob.phillips1 (6510)
6/25/2005 8:54:52 AM
My original message stated that I couldn't subtract from abov
10,000:00. It works fine for any time below this

--
fishphon
-----------------------------------------------------------------------
fishphone's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2462
View this thread: http://www.excelforum.com/showthread.php?threadid=38211

0
6/27/2005 5:29:30 PM
Reply:

Similar Artilces:

Time mail was opened
Is is possible to tell on exchange server 2000 or through outlook what time an email was first opened? The client is using outlook 2003. I've been searching for several hours and haven't come up with any mention if this is even possible. Can anyone answer this or point me in the right direction? tia, Kevin Here is a close resemblance. First go to the message tracking tool in Exchange System Manager and checked when it was delivered into the (local) user's mailbox. Second go to the Information Store where that user's mail box is and see when was the last time the user lo...

Run Time Acces 2007
Hello, Run-time for Acces 2007 seems to be bundled in the Office 2007 dev-kit that should be free. Does anyone heard about a release date ? thanks -- Nicolas Best information is found here: http://blogs.msdn.com/access/archive/2007/03/26/running-access-2007-in-runtime-mode.aspx -- Jeff Conrad - Access Junkie - Ex-MVP Software Development Engineer in Test XAS Services Microsoft Corporation Co-author - Microsoft Office Access 2007 Inside Out Presenter - Microsoft Access 2007 Essentials http://www.accessmvp.com/JConrad/accessjunkie.html Access 2007 Info: http://www.AccessJunkie.com -----...

Sort column with multiple values
Is there a way to (data) sort columns so that multiple values are recognized? I have a column labeled lawsuit type where I classify cases (.i.e. fraud, discrimination). Some lawsuits fall into multiples categories (both fraud and discrimination). Right now it's just sorting in alphabetical order and only recognizes the first word I write in. Try Data > Filter > AdvancedFilter, and follow the menus........once set up, it will allow you to filter on multiple selections in a single column.......... Vaya con Dios, Chuck, CABGx3 "gildedlily" <gildedlily@discussions...

problem setting the value from a function
Hi: I wanted this macro to accept the date the user gives in the dialog box. In all the sheets on the range v6 it should put that date but it should only be on sheets2 and onward not on sheet1. I wanted it to only ask for the date once. This macro asks for the date on every iteration and I don't know how to set the value for the variable inputDate? It doesn't change anything if I move the variable on the right or left side of the function. Thanks again. Sub months1() Dim inputDate As Date Dim i As Long inputDate = InputBox("Enter a date:", "Date", Date) For i ...

Radio Button Value
I have minimal knowledge on radio buttons. I am designing a QA table and form to be used to grade reports at work using 1 - 5. I want the Supervisors to grade each question by choosing a radio button 1 - 5. I will add the numbers up in a report for viewing. My question is how can I calculate the total without using an IF statement to see which button was chosen? Thanks, Les On the form, create an Option Group for each question, and assign the values 1-5 for each label. Then you can just add up the values via the Option Groups and not use any if statements. I'm no...

Get count of unique field values
Hi. I have a table with a column called Employee ID. Many rows can have the same Employee ID. I need to get the total number of unique Empolyee IDs and use this number in a formula. What's the best way to do this? I'm wondering if I should make a Pivot table that provides this number, but I'm not sure how to do that. Or perhaps there is some other approach. Any info would be greatly appreciated! Thanks, Ken Hi Ken have a look at http://www.cpearson.com/excel/duplicat.htm#CountingUnique for some formulas depending on your data structure -- Regards Frank Kabel Frankfurt, ...

time clocks from a 3rd party certified partner
I am looking for time clocks that will fully integrate with our GP PR. -- KSR Bean Count in Mfrg There are plenty. Kronos and Unitime are two ends of the spectrum. But, the important thing is the timeclock works the way you need it. The timeclocks my clients have used have all created a file imported with Integration Manager. The key is creating that file so you want to make sure the Timeclock program is flexible enough to create the file using the payroll information you need. -- Charles Allen, MVP "KSR" wrote: > I am looking for time clocks that will fully integra...

macro run-time dialog box causing integration manager problem in GP9.0
I'm using AutoDim and windows scheduler to run integrations in integration manager. In GP7.5 it all worked fine but now that I've upgraded to GP9 there is a macro run-time dailog box that's causing problems. I see the documentation on how to supress the dialog within a macro using Logging file '<filename>.txt' but this doesn't work. I'm thinking it's a timing issue or the fact that the macro starts outside the scope of the dynamics runtime (AutoDim is calling the macro) so once dynamics is open and running the logging file command is out of scope? Is ther...

Count YEAR value?
I need to count the number of certain years in a row with date values. Something like "number of cells in a1..a99 where year=2008" I tried various ways of using COUNT and YEAR functions, but haven't found the answer. Thanks, Ole Thomsen Try this... =SUMPRODUCT(--(YEAR(A1:A99)=2008)) -- Biff Microsoft Excel MVP "Ole Thomsen" <ot@networks.dk> wrote in message news:A5A9A792-525B-41A8-A7B6-9E99C2A7B4F5@microsoft.com... >I need to count the number of certain years in a row with date values. > > Something like "number of ...

Count how many times the same word appears in column
Hello, I have a long list of about 50,000 words in column A. Some of these words are the same. I want to know the exact number of times each word appears in the A column. And I want to sort these words based on how many times they appear in the A column. How can I do that? Alexa =countif(A:A,B1),if B1= you word In B1, add =COUNTIF($A:$A,A1) Copy down Then sort by column B -- HTH RP (remove nothere from the email address if mailing direct) "Alexa" <forums@discountcosmeticsguide.com> wrote in message news:5035b11a.0504160444.6b89e3a2@posting.google.com... > Hel...

Bulk upload of pick list values
Hi, I have created some new fields in CRM which are picklist type. Is there any way to bulk upload picklist values into that field so I would not have to type them manually? The problem is I have some picklist fields which should comprise more than 100 picklist values and to think of typing them manually .... Thanks. Unfortunately you do have to type all the values in the picklist manually... "Mikkhail" <Mikkhail@discussions.microsoft.com> wrote in message news:86D6D7CF-25E4-4658-8FAD-FF9DE934AD5A@microsoft.com... > Hi, > > I have created some new fields in C...

Linking Combo Box Values to Access Table
Hello, I am trying to create a combo box and link it to values in an access table. The access table has a list of well names, dates, rates and hours and several other columns that I am not interested in. I need the combo box to be populated with the well names, and when I select one I would like it to return 3 columns with the Date, Rate and Hours. I have already tried Data>External Data ... but the problem is that there are too many columns for excel and therefore I am missing a lot of my data, so I need it to pull directly from Access. To summarize 1) Create combo...

mutual funds arbitrary value
My US Bank 401k allows me to buy First American Mutual Funds. However, they set an arbitrary share price value which doesn't jive with the actual mutual fund value. There are no download features either. Any ideas on how to factor out this mess? Mike Isn't that the US Bank sweep vehicle? Is this an actual investment, or just cash? "The Glidster" wrote: > My US Bank 401k allows me to buy First American Mutual Funds. However, they > set an arbitrary share price value which doesn't jive with the actual mutual > fund value. There are no download features...

Use Different Delivery times for oversize messages
Hi. I having a problem with setting up the option "Use Different Delivery times for oversize messages" When I set the option in the SMTP connector its all look fine. And when I send a mail that is big in size than I can see in the queues that it is schedule to be sent at night. the problem : when it's time to send the mail the server is not sending them... or sometimes it is sending only one mail and the rest is "stuck" Any ides??? Thanks in advance Another thing that I have been notice is that in the queue's you can see that only one message is schedule...

Time calculations #3
Excel 2003 I want to try to put together a store employee schedule for my son and I am wondering what is the best way to deal with time calculations. they do not use a 24 hour format so how can I do calculation (shift times, Employee availability times, sum of hours......) when the times are AM/PM format. Any guidance here will be appreciated. Thanks dave You can add and subtract times irrespective of the format. 17:00 less 08:00 returns 9:00 the same as 5:00 PM less 8:00 AM returns 9:00. You will need to use the number format to format answers correctly. When summing hours that ...

returning values from other worksheets
I have a worksheet with checks that have been written. Column A, check number column B, amount. I have another worksheet with checks that have cleared the bank. Column A check number and column B amount. I want to search Column A for matches to column A and return the amount in column B for the amount the check cleared the bank for so I can compare it to the amounts written. These lists are huge and would take weeks by hand. Hi have a look at VLOOKUP for this: http://www.contextures.com/xlFunctions02.html -- Regards Frank Kabel Frankfurt, Germany "Arch" <Arch@discussions....

AutoSave File After X Time In VBA #2
Thanks for the help....... But is there a way to create a macro that will save the orriginal fil periodically, instead of relying on the autosave function on Excel? Maybe using the SaveAs and Wait functions? Celtic_Avenger :confused: :confused: :confused: :confused: :confused -- Celtic_Avenge ----------------------------------------------------------------------- Celtic_Avenger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1410 View this thread: http://www.excelforum.com/showthread.php?threadid=26126 Look at application.ontime Chip Pearson explains i...

Linking or Vlookup actual values
I have a workbook with 3 worksheets: Actual Weekly Units Ordered, Projected Weekly Units Ordered and Analysis. My column headings are set up by dates for all 3 worksheets, 1/2/2006 thru 12/25/2006. In the analysis worksheet, I need to pull in the Projected Weekly Units Ordered, however as the current dated week comes up and the Actual Weekly Units Ordered is entered, I need to have the Analysis Worksheet pull in the Actual Weekly Units Ordered for the then current week in lieu of the Projected Weekly Units Ordered for that week. I hope I made myself clear. Any help would be apprecia...

Cannot refer to values of cells in another worksheet
I have a worksheet named Items. I am trying to refer to the values in another worksheet called Dist (same workbook). The value in the Dist worksheet at F6 = 1906, and the value in Dist worksheet at AO6 = 100. So at Items A1, in the formula box, I type =Concatenate(Dist!F6," ",Dist!AO6) because I want to refer to the numbers as text. But instead of showing me 1906 100 all I see is the formula I typed instead of the formula's value. When I type the same formula in a cell for the Dist worksheet it works fine, but when I change to another worksheet it doesn't work.What am I doi...

Forms time calculation
I am attempting to create a calculation in a form that will still enter it into the main table. I am attempting to calculate total hours of down time from entries in the DT Months, DT Weeks, DT Days, DT Hours, DT Minutes entry boxes. My calculation might look something like this: Down Time=(((DT Months*21)*24)+((DT Weeks*5)*24)+(DT Days*24)+DT Hours+(DT Minutes/60)) I would like it to display in the form as well as the table the form updates. I am a learn by trial and error and asking questions novice at MS Access and would like to get as much help as I can, please. Thanks to anyone who...

Installing Project Time and Expense on Business Portal
When I try to install this component, it tells me that Project Time and expense is not installed/registered in the backoffice. Now, I do have Project Accounting installed, up and running. How do I get Project Time and expense to register in the backoffice Hi We had the same problem but now solved this. First you need to make sure Project is registered in GP. Then you need to setup Project in GP (system level), once done you should be able to install BP 2.5 with Project time and Expense. If you have not setup Project in GP (after the Project installation etc..) you will still get t...

CDC
I'm drawing multiple pie-shaped regions all bounded by the same elipse (so a whole pie cut up into multiple slices). The problem is that I have to draw over the center point multiple times in order to close each individual pie-shaped region. This is causing the center point to grow in size. How can I avoid this? Is there a CPen style that accomdates this? Thanks, Jeff for(int i=0;i<m_volumes;i++) { dc.MoveTo(m_center); dc.BeginPath(); dc.AngleArc(m_center.x,m_center.y,m_radius,i*m_sweepSize,m_sweepSize); dc.CloseFigure(); dc.EndPath(); dc.StrokePath(); } ...

onchange()-: keep original value after save instead of the new val
Hi, I write a Java Script in the OnChange() and when I save it without click on the field it doesn't want to save. I add the click() function at the end but it didn't change any thing. This is my code, did somebody see something wrong? Thanks var x = crmForm.salesstagecode.value;var y = "0";switch (x){case "01 - Not Qualified - 0%":y = "0";break;case "02 - Prospected but customer not yet engaged - 10%":y = "10";break;case "03 - Engaged but not yet proposed - 30%":y = "30";break;case "04 - Solution propos...

Get time from a Date & Time field
I have two cells that contain a date & time, and are formatted as =93ddd dd/mm/yyyy hh:mm=94. What I need to do is put a formula in another cell that will display a =91Y=92 if the time in the first cell is after 08:00 and before 17:00. Can I do this, and if so how? You can split the date part off by using this: =3DINT(A1) and the time part off by this: =3DMOD(A1,1) or this: =3DA1 - INT(A1) assuming the date/time is in cell A1. So, to do what you want with one formula you can try this: =3DIF(AND(MOD(A1,1)<17/24,MOD(A1,1)>8/24),"Y","N") Leaving the fract...

Ignore Hidden Rows and Count of distinct values
Hi, I need to display the Count of distinct values and also ignore hidden rows in the count. Please help me to fix this issue. Look in the help index for SUBTOTAL and pay special attention to hidden rows part. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Anuma (GGK Tech)" <AnumaGGKTech@discussions.microsoft.com> wrote in message news:8A8B8ACB-7912-4554-90A7-5EE4987A1616@microsoft.com... > Hi, > > I need to display the Count of distinct values and also ignore hidden rows > in the count. > Please help me to fi...