time calculation

[Siteman]

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?

 

[Glenn]

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?

=ROUNDUP((B1-A1)*24,0)

 

[Siteman]

Thanks for your quick response. this did not work, it came back with an
error in value message. I am wondering if it has to do with the am and pm
that is in the cell with the times. also do i have each cel formatted
properly?

the cells have this exaclty

start end hours
11:10am 01:00pm

i have the time cells formatted in custom, with the following

Time cells h:mm AM/PM
and the total hours, formated as ,, number -123.10
any other ideas?

 

[David Biddulph]

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.

 

[Siteman]

It looks like it defineatly has to do with the export having the am/pm in the
number cell,
if i find all am and pm and replace with nothing, in other words remove the
refernce, then excel automatically puts in caps AM - which of course does not
work either. I suspect now it has to be on a 24 hour clock so that excell
can figure that out, i will try this let you know how it works out.
hopefully the software that i am exporting from has this option.

 

[Glenn]

It looks like it defineatly has to do with the export having the am/pm in the
number cell,
if i find all am and pm and replace with nothing, in other words remove the
refernce, then excel automatically puts in caps AM - which of course does not
work either. I suspect now it has to be on a 24 hour clock so that excell
can figure that out, i will try this let you know how it works out.
hopefully the software that i am exporting from has this option.

See David Biddulph's response. Basically, replace "AM" with " AM" and replace
"PM" with " PM". Then see if it works for you.

 

[Siteman]

yes this is indeed the problem, there is no way for me to convert to a 24
hour clock in the exporting software, it exports times like this

01:00pm which would be 13:00 on the 24 hour clock. when i format the cells
to
h:mm AM/PM, and then remove the exported am/pm from the cells, it converts
it all to am, as it was exported on a 12 hr clock.

Is there a way to convert anything that has a pm at the end of it to a 24
hour clock without doing a macro for each time.

in other words,

can i have all of my 12 hour clock times in the pm converted to a 24 hour
clock with some kind of formula to seek and convert?
3:50pm = 15:50pm?
every number from 01:00 - 11:00pm = add 12?

just trying to find a way to convert the data, so that excell can work in
the 24 hour clock. and so i dont have to week through a dozen or so macros

 

[Siteman]

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?

 

[Siteman]

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!