Calculating Time- Formula Error?

[DougStroud]

I am trying to do a simple timesheet calculating hours worked:
10:35a-5:15p
Formula: b1-a1

A1 and B1 are formatted as time with hours, minutes
C1 is formatted as time in custom: h:mm

Results are- #Value !

 

[DougStroud]

I found one work around- if I enter 10:35 am and 5:15 pm it then resolves. But how persnickety is this, rather than a simple "a" or "p"?

 

[MARK858]

But how persnickety is this, rather than a simple "a" or "p"?

When would you ever say when you are talking to someone about time 10:35a rather than 10:35am? 10:35a just isn't way of a common way of referring to time so why would Excel treat it as a time rather than text?

If you have a lot to change use the REPLACE function.

 

[Tetra201]

It's not about "a" vs "am", it's the space between the digit and the letter. Excel does understand "10:35 a".

Try using this formula:

=SUBSTITUTE(SUBSTITUTE(UPPER(B1),"P"," P"),"A"," A")-SUBSTITUTE(SUBSTITUTE(UPPER(A1),"P"," P"),"A"," A")

 

[DougStroud]

Thank you