got it
the mistake i made was converting to the 24 hour clock, all i needed to do
was add the spaces
and the formula
thanks everyone!

"Siteman" wrote:

ALmost there
by converting to a 24 hour clock then inserting the space as you laid out
below, it works, however it is not calculating all cells,

using this formula here is the results =ROUNDUP((C3-B3)*24,0)

any number in the 12 hour clock works, but in the 24 our clock it has an
error in value,
for example 9:00 am - 10:50 am = 2 (hours) good
11:10 am -13:00 pm = error ( should be 2 (hrs) )

i notice something else, the cells containing pm are not calculated again
referring to the 12 clock


ideas?












"David Biddulph" wrote:

Remember that the format, if you wish to use AM/PM, does include a space
before the AM or PM.
It is 2:00 PM, not 2:00PM.
It is no good just formatting the *display* of the cell if you haven't
entered it correctly, as an incorrectly entered string will be treated as a
text string, and cell date formatting has no effect on text strings.

If you've got a vast amount of data incorrectly entered, you can use Edit/
Replace to change
AM
without a space to
AM
with a space at the beginning of the string, & similarly for PM.
--
David Biddulph

Siteman wrote:
i have read and tried all of the threads on calculating time
difference but none of them work.

Specifically
i have done an export of data to excell the times come out like this,
A B C
start end
2:00pm 3:50pm 1 ( i want to round this to the nearest hour)

I formated the above cells h:mm pm
and the formula cell as general with decimals

i have tried =b1-a1
=int((b1-a1)*24)

none of which work, if i delete the pm or am it will calculate, but
not accurately.

I want to round up the time calculation to the nearest number,
ie, 2:00pm 3:50pm 2(hours)

any thoughts?