Subtracting Time

[Guest]

A B C

2 Start Time End Time Hours Worked
9:00am 11:00am #Value

I have cell A2 formatted as time 9:00am
I have cell B2 formatted as time 11:00am
I have cell c2 formatted as 13:30

In cell c2 I have formula =b2-a2+ if(b2>a2, 1)
I received an #value error.

Sometimes I can get the formula to work correctly if I type 12:00am in A2
which shows 1/1/1900 12:00:00 AM in the formula box above and when I type
9:00am in B2 in the formula box above showes 9:00:00 AM. But if I change b2
to say 8:00am then I get a #value error again.

I tried looking at all the postings to see if there were similar postings
and tried their formulas, but still get the same error. Wondering if I have
a conflict with my software.

Thanks
Lisa

 

[J.E. McGimpsey]

One possibility:

If you're not putting the space between the last digit and the "a"
in "am" then your entry will be treated as Text, which will give the
#Value! error.

Also check that your entry cells aren't preformatted as Text

 

[Guest]

One possibility:

If you're not putting the space between the last digit and the "a"
in "am" then your entry will be treated as Text, which will give the
#Value! error.

Also check that your entry cells aren't preformatted as Text

Unbelievable!

I looked at those cells so many times and couldn't figure what the problem
was! It was just like you said....I didn't have a space after my time and
before "a" in am! It was that simple!!!

Thank You!