keep getting #Value

I am trying to calculate the difference of two fields date and time
09/01/2009 12:04AM  in B2
09/01/2009 01:22PM   in E2


=TEXT(E2-E2,"h:mm:ss")

=E2-B2

I tried the above formulas - both return #Value!  - this is imported data - 
I reformatted since it was General and I made them Both DATE - still get the 
Value as answer - just want the response time difference- Help!!

thanks soo much,
Barb
0
Babs (23)
10/11/2009 9:05:01 PM
excel 39879 articles. 2 followers. Follow

3 Replies
397 Views

Similar Articles

[PageSpeed] 8

On Sun, 11 Oct 2009 14:05:01 -0700, babs <babs@discussions.microsoft.com>
wrote:

>I am trying to calculate the difference of two fields date and time
>09/01/2009 12:04AM  in B2
>09/01/2009 01:22PM   in E2
>
>
>=TEXT(E2-E2,"h:mm:ss")
>
>=E2-B2
>
>I tried the above formulas - both return #Value!  - this is imported data - 
>I reformatted since it was General and I made them Both DATE - still get the 
>Value as answer - just want the response time difference- Help!!
>
>thanks soo much,
>Barb

Assuming you "cut and pasted" the above values, they are both TEXT values and
not Excel dates.

The #VALUE! error will be returned when you try to do arithmetic on text.

I can tell they are text because, if they were really Excel dates, there would
be a <space> prior to the AM or PM.  But you can place a formula =istext(e2)
someplace and see what it returns.

Changing the cell format will NOT change the contents of the cell from TEXT to
date.

To convert these text strings into something Excel can interpret as a date,
try:

=REPLACE(E2,LEN(E2)-1,0," ")

Substituting in your formula, we have:

=REPLACE(E2,LEN(E2)-1,0," ") - REPLACE(B2,LEN(B2)-1,0," ")

and format the result as h:mm:ss, or whatever you want.

Substituting those functions within your TEXT formula should also work.
--ron
0
ronrosenfeld (3122)
10/11/2009 10:17:34 PM
=text(REPLACE(E2,LEN(E2)-1,0," ") - REPLACE(B2,LEN(B2)-1,0," "),"h:mm:ss")
this works to get time elapsed but now I wan to figure the number of Working 
days???elapsed

I put in =NETWORKDAYS(REPLACE(E7,LEN(E7)-1,0," "),(REPLACE(B7,LEN(B7)-1,0," 
")))  and when it should be 1 day plus some minutes it only show whole #'s 
and many of them are Negative even if the workdays should be + B has start 
time/date and E has end time and date

thanks sooo much for helping!
barb
"Ron Rosenfeld" wrote:

> On Sun, 11 Oct 2009 14:05:01 -0700, babs <babs@discussions.microsoft.com>
> wrote:
> 
> >I am trying to calculate the difference of two fields date and time
> >09/01/2009 12:04AM  in B2
> >09/01/2009 01:22PM   in E2
> >
> >
> >=TEXT(E2-E2,"h:mm:ss")
> >
> >=E2-B2
> >
> >I tried the above formulas - both return #Value!  - this is imported data - 
> >I reformatted since it was General and I made them Both DATE - still get the 
> >Value as answer - just want the response time difference- Help!!
> >
> >thanks soo much,
> >Barb
> 
> Assuming you "cut and pasted" the above values, they are both TEXT values and
> not Excel dates.
> 
> The #VALUE! error will be returned when you try to do arithmetic on text.
> 
> I can tell they are text because, if they were really Excel dates, there would
> be a <space> prior to the AM or PM.  But you can place a formula =istext(e2)
> someplace and see what it returns.
> 
> Changing the cell format will NOT change the contents of the cell from TEXT to
> date.
> 
> To convert these text strings into something Excel can interpret as a date,
> try:
> 
> =REPLACE(E2,LEN(E2)-1,0," ")
> 
> Substituting in your formula, we have:
> 
> =REPLACE(E2,LEN(E2)-1,0," ") - REPLACE(B2,LEN(B2)-1,0," ")
> 
> and format the result as h:mm:ss, or whatever you want.
> 
> Substituting those functions within your TEXT formula should also work.
> --ron
> 
0
Babs (23)
10/12/2009 2:31:01 AM
On Sun, 11 Oct 2009 19:31:01 -0700, babs <babs@discussions.microsoft.com>
wrote:

>
>=text(REPLACE(E2,LEN(E2)-1,0," ") - REPLACE(B2,LEN(B2)-1,0," "),"h:mm:ss")
>this works to get time elapsed but now I wan to figure the number of Working 
>days???elapsed
>
>I put in =NETWORKDAYS(REPLACE(E7,LEN(E7)-1,0," "),(REPLACE(B7,LEN(B7)-1,0," 
>")))  and when it should be 1 day plus some minutes it only show whole #'s 
>and many of them are Negative even if the workdays should be + B has start 
>time/date and E has end time and date
>
>thanks sooo much for helping!
>barb

1.  If you look at HELP for the NETWORKDAYS function, you will discover that it
only returns "DAYS", not "TIMES".  Hence seeing whole numbers is by design.

2.  If you are getting negative numbers where you think you should be getting
positive numbers, then either you are misreading what you are seeing, or the
format of the dates you are putting into the REPLACE function are not identical
to what you posted earlier.

3.  If you are going to be doing calculations on these results, it'll be
simpler (and easier to debug), if you just set up a "helper column" with the
REPLACE function and format it appropriately.  It'll also let you see if your
data entry format is consistent, and that REPLACE is doing the job.  If your
data is NOT as you posted, then the REPLACE method will not be reliable, and
you will need to use a different, somewhat more complex method.

--ron
0
ronrosenfeld (3122)
10/12/2009 10:07:25 AM
Reply:

Similar Artilces:

Want to average values grouped by value in a certian column (sql example included)
It can be done with SQL using: SELECT Day, AVG(Users) Avg Users FROM NOTESMON GROUP BY Day But that is not very portable On the attached sheet the statement needs to take every value from th User column (J) where the Day column (H) was the same, add them up, an take the average of them. So if Day was 30 for the first 10 rows it would take the value in th users columns for those 10 rows, get their average, and plop it ou next to the vaule of the day, so it would be 30 and then next to tha the average value of all the entries of User that correspond to 30 Advice Attachment filena...

return first value
Hi I have a table (sheet1) that contains. Date column(A), ID column (B) and Time column (D) In a new sheet i need to populate (sheet2) cell C2 and D2 based on the following Conditions. (sheet2) column (A) contains date for each record, column (B) contains ID. note: sheet1 has been sorted by Date, time and ID C2 = sheet1A:A,>=sheet2!A2 and sheetA:A,<=sheet!A2 and Sheet1B:B,=Sheet2!B2 then return First value from sheet1,C:C. D2 = sheet1A:A,>=sheet2!A2 and sheetA:A,<=sheet!A2 and Sheet1B:B,=Sheet2!B2 then return Last value from sheet1,C:C. :confused: Thanks :mad: -- mathewhey...

Exclude Hidden Row Values
Is there a way to exclude counting values in a total when rows are hidden and including them when they are not hidden in a COUNT, COUNTIF, and COUNTA formula? Thank yoiu for any help. -- Dewayne What version of Excel are you using? If you're using Excel 2003 or later you can use the SUBTOTAL function with the 100 series arguments. -- Biff Microsoft Excel MVP "Dewayne" <Dewayne@discussions.microsoft.com> wrote in message news:D5E5D81A-2C83-4879-9F82-5EC3925144C1@microsoft.com... > Is there a way to exclude counting values in a total when rows are...

text search for a non-constant value?
Hi! The seach/3 function searches for 'find_text' in 'within_text', starting at the 'start_nun's character: SEARCH(find_text,within_text,start_num) I need to find the first digit in a string. I guess, search/3 does neither support regular expresseions, no does it allow a function instead of a constant 'search_text'. One way to find the first digit is this monster: =MIN( IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999); IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999); IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999); IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9...

Fields revert back to original value
Hoping someone can help. I have 2 calculated fields on a quote order line. I calculate the values using javascript and display them as the fields that go to make up the calculation are changed. Everything works fine until I reopen the record and the values have reverted back to their original value. I want the calculated fields to be read only as they are margin calcs and salespeople are compensated on margin value. If I set them to be editable everything works fine. On Feb 24, 12:04=A0pm, riords <rio...@discussions.microsoft.com> wrote: > Hoping someone can help. I have 2 calc...

How to get notification of menu click added dynamically???
Hye, This is Jigar mehta from India. I have made one application that adds dynamic menu items from the database. Each menu item has one ID and menu item's text is coming from the database. Now, I want to get the notification of the menu item click which is added dynamically. I want to put one common routine where I can distinguish between all menu item clicks. Menu Items can be two or hundred. So, how to make one handler that will be invoked with parameter of the clicked menu ID... Please help me out... -- Jigar Mehta jbmehtain@yahoo.co.in "Jigar Mehta" <jbmehtain...

Any way to get Freeze Panes to work with Split View?
Freeze Panes disappears if I split the view. Worse is if I try my general technique of selecting row or column prior to splitting view to split as I want it, it always does the opposite, and then when I do it again moves me to the top column losing my place (this if i try to do so with Freeze Panes on. I often need Freeze Top and Split Columns anyway to do so? You cannot split and freeze on same sheet in same window. One or the other. Posiible workaround.................Window>New Window. Split or freeze either or both windows. Gord Dibben MS Excel MVP On Mon, ...

Keeping Leading 0's in Hyperlink Displayed Text
Dear XLRS, I am trying to do something that may be impossible. I am using a we query to grab a list of Hyperlinks which point to specific models in m warehouse. An example of a Hyperlink I would like to pull from th website is: _0299921_ When I web query this page into Excel, the Hyperlink will show up a _299921_, as you can see the leading 0 is gone. I am unable to grab th model number from the Hyperlink address, so that is not an option neither is formatting the cell to Text, as this will cause al hyperlinks to be ignored! Does anyone know how to prevent Web Query, or Excel itself from cut...

How to set text property of a check box to value of a cell?
I am building an Excel template. It has one worksheet with data (called "data") and many worksheets with craphics, built from the "data" sheet. One sheet with radar-type chart has number of check boxes which control hiding/unhiding columns. When a cloumn is hided, it does not produce a line on radar chart or a bar on bar charts. In the legend area user can see names of all series (=columns), used to build charts. But check boxes have their texts hard coded. I need to code a subroutine, which will be executed when a sheet with radar chart will open. It should assign...

In a formula, how do I set a maximum acceptable value?
I need to calculate my loss on a rental property but the loss cannot be greater than $25,000 no matter how large it actually is. How do I write an Excel formula that will include this maximum value limit? Use the Min function, as in: =min(25000,yourformula) Regards, Fred "hollisterbulldawg" <hollisterbulldawg@discussions.microsoft.com> wrote in message news:E8CCDA8F-F60E-4D48-BDE8-8B35760E2BD3@microsoft.com... >I need to calculate my loss on a rental property but the loss cannot be > greater than $25,000 no matter how large it actually is. How do I write...

Two variables for Target.Value
Below is my code that works for any items identified as "In Progress" what if I want to follow the same process for both "In Progress" and "Not Started"? how would I udate the code to include both. Thanks! Private Sub Worksheet_Change(ByVal Target As Range) Dim Dest As Range If Target.Count > 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 7 And _ Target.Row > 4 And _ Target.Value <> "In Progress" Then With Sheets("Complete") Set Dest = ....

Arrange B values ascending order but keep attached to column A in.
I have two columns of data in XLS, A is the years the data was collected and B is the data for those years. I want to arrange the numerical data in column B in ascending order but keep the corresponding years for the data attached to them. The trick is to select BOTH cols A & B and then sort by B ascending. The values in col A will stay with the associated values in col B. So if we start with: 1/22/2010 1 1/23/2010 69 1/24/2010 71 1/25/2010 29 1/26/2010 31 1/27/2010 9 1/28/2010 64 1/29/2010 49 will end up with: 1/22/2010 1 1/27/2010 9 1/25/2010 29 1/26/201...

Cant get Money to work
when I try to use monwy to set up new account, all I get is "The list is temporarily unavailable. Please try again later." This is the second night in a row that it has done this. Did I buy something that does not work???? In microsoft.public.money, Roaddog_tnkr wrote: >when I try to use monwy to set up new account, all I get is "The list is >temporarily unavailable. Please try again later." This is the second night >in a row that it has done this. Did I buy something that does not work???? What did you buy? What are you trying to install it on? On De...

Excel getting info from Access
I have recorded this macro that modifies the information that I am pulling into Excel from an Access query. About half way down, there is a line that says, "WHERE (invexcption.Area='US006')". I know that I can modify the code to bring back another areas information. for example, invexcption.Area='US007'. I would like to be able to make the Area a variable, so that I can create and save a different file for each area... but cannot seem to get it to work. Does anyone have any thoughts? With ActiveWorkbook.Connections("Query from MS Access Da...

Checking another record has the same value
Hi, I am not sure how to go about this so any help or advice would be greatly appreciated. I have a table, tblContactOrganisation, which is a Many of a singular tblContact. I would like to make only one record in those records for that specific Contact to have a true value for the field chkPrimaryAddress. So when the user click the chk box Primary Contact it checks to see if any other Primary Addresses have been ticked, if so it unchecks the old Primary address and checks the new Primary address after user acceptance. Any advice would be greatly appreciated on how i would go about startin...

How do I get a product code for a trial download of office?
I also want to know if buying MS software from Amazon is legal. "retiredlady" <retiredlady@discussions.microsoft.com> wrote in message news:2926B22E-108A-46F2-993A-32C3CBA2EDE9@microsoft.com... > I also want to know if buying MS software from Amazon is legal. Of course it's legal. Why would you think it's not? Yes it is. I assume you're buying the retail version so you'll have the CDs and the product key will be on the cases. Amazon is a great place to buy Office - they are usually several dollars cheaper than buying it from Microsoft. ...

I cannot get email from all aol users
Family is converting over to Microsoft Outlook 2003 but we cannot receive email from everyone in aol. We do receive it from several people but I cannot even recieve it from my own aol account. When I send from aol I do not get an error message stating it is not being delivered. Is this in the set up of Outlook or Aol and where do I look to fix the problem. thanks bob Not here, for sure, ask AOL. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "bobhsgolf" <bobhsgolf@discussions.microsoft.com> wrote in message n...

Set value of drop down list
I've created a drop down list(list A) in a cell, using the data->validation list creating thing. However based on the selection of another list(list B), i want to default the values of list A, to one of the list items. I was trying to do it in VBA, but i couldn't just change the cell.value. The only way I could change it was by selecting the corrrect one in the sheet. thanks in advance for the help. -Ricky I was able to do it with DV using Range("E1").Value = "Lynne" I could even set a value not in the list without complaints. -- HTH RP (remove n...

How to preserve null value in a access calculated field using iif
Hi, I have a field in sql server table named hasmajor. Now this field is of type smallint. The front end is access connected to sql server via odbc. Now the field value in Access for this field is: 1 0 0 1 0 1 I am using a function =IIf([HasMajor],"Y","N") as the control source for another column that will display the above field value as Y N. I am expecting the values of the above field in the calculated column as the following: Y N N Y N Y This means I am expecting neither N or Y when there is null value. However the null value is being displayed as N. I have ...

copy text box value from current form to "Next" form
I am creating a DB to handle/record a process that has basic information (Table1), I have a data entry form (Form1) for this table/information that includes a “ID1” field (stored "ID1" is TEXT and generated by VBA code “beforeinsert” and is unique/key). I would like to have command buttons (at least 3) on this form that will when selected (clicked): 1). Mark a corresponding Yes/No box in “Table1” (“ReqAction1a”) to “True” for later querying (not visible on form). 2). Then copy the value in “ID1” from “Form1” (for relating tables, one to many and user reference). 3). Open “Form2” (dat...

I have to repeat my password for e-mail every time, how do i get .
Every time I go to my inbox I have to put in my password. It is very frustrating but I don't know how to get rid of it. I am using outlook express. windows xp. and I have a compac cp. I do have service pack 2 installed and all updates are ok. my ISP server tells me I have a glitch on that program and that I have to reinstall this e-mail account. I would try to do that but how do I uninstall outlook express and then reinstall it again. My server told me to do that. Outlook express came with my cp. so I do not have a disk or cd to put in and reinstall it. Any help anyone can give me...

Keeping the blue caret visible even when focus lost
Applies to CTreeCtrl and CListCtrl (among others)... A while back I asked in a VB group if it was possible to keep the caret visible even if focus changes to another component, and the answer was yes. How does one do that with MFC (in VC.NET)? Thanks a lot In Advance! Specify the TVS_SHOWSELALWAYS & LVS_SHOWSELALWAYS styles respectively. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "loquak" <liam_parc@hotmail.com> wrote in message news:%239vocCJLEHA.2148@TK2MSFTNGP09.phx.gbl... > Applies to CTreeCtrl and CListCtrl (among others)... &g...

Combine pull down + Vlookup return values in multiple cells
I am working on a project that I believe shoud have pull downs and vlookup to return the values to multiple cells. My concept is this.... I have a pulldown on a Form! page in cell b3 It pulls values from tables! in my workbook with values ranging from a1:a10. I would like the returned result.... for example... Michael to then lookup on tables! from a range of c15:e500 the following information. The values of column C are Names column D are job Numbers column E are job descriptions. so the pull down results from b3 on the forms page will limit the possible names in column C from Tables! ...

Getting a report to show a looked up value
I had a report in access 97 that worked fine. When converted to access 2000 it does not report the same. The report is based on a query. In the query, one of the columns refers to a table and the field in that table is a lookup value. The lookup field looks for a customer's number and then goes to the next column to report the customer name. When the query shows the data, it does not show the customer number - but it does show the customer name - and that is just what I want. However, when the report comes out, it does not show the customer name - it shows the customer number. Any h...

Why am I getting a server busy dialog box? #2
I'm having a problem - i'm getting a "server busy" dialog box (the one with the "switch to" and "retry" buttons) and have no idea why. I have tested my software on some machines and it works fine, however on others it causes this problem. I've written a component that is called as a microsoft dynamics rms v2 hook. The component is written for the .net 2.0 framework and is also registered as com callable. It exposes a very simple interface: one method which blocks until it completes its work, and then returns a boolean to indicate success or fa...