keep getting #Value

[babs]

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

 

[Ron Rosenfeld]

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

 

[babs]

=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]

=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