Excel mm:ss strings and rounding up

[Marcos]

I have text that I am trying to paste into an excel page. I have
worked out everything. I even had the time functions working in the
original, but when I pasted new data, Excel decided to put the times
into geek time! As I am a geek, and dont uderstand these times , How
can I possibly round them up to the minute?

here is some sample data

2003.06.29 10:38:11| pavia2| +52687896XXXX | Mexico | 69:48
2003.06.29 10:35:45| Pavia1| +5296262XXXXX | Mexico - Mobile | 15:31
2003.06.29 10:31:26| pavia1| +1209XXXXXXX | USA | 0:14
2003.06.29 10:30:56| pavia1| +1209XXXXXXX | USA |0:18

Now The above is pasted into a worksheet called "Data" I have used "|"
seperators to help seperate the fields here. My goal is to take the
5th field "E" and make it round up to a whole minute for calculations,
as we do not bill by partial minutes.

In a seperate sheet I have the following that refers to the data
above.

=SUBSTITUTE(Data!E3,":",".")
I used this in attempt to later use a ROUNDUP function. In this case,
if the call is more than 3:00, it is billed as 4.00, so there is
really no difference between it being 3.24 or 3:24. The simple fact
is, that it is MORE than three minutes, therefore four minutes The
data that comes out of this result however is drastically different
than the original. It is translated into geek time before it gets
there.

The problem is,in the original data that I worked with,it was fine.
The characteristics of the cells seem to change when new data is
pasted into them. This makes it impossible as in the case of
"=SUBSTITUTE(Data!E3,":",".") " the result is something like
0.00849537037037037,a totally useless number for me and how would I
even begin to round that up to a minute? I thought I had it licked the
problem, with the substitute function, but it seems to change. If I
format the original cell for text , then MANUALLY enter the data it
seems to work, but even changing the cell to text after entry causes
the microsoft psycho babble geekspeak above. I can not manually enter
thousands of these fields monthly , that would be far too cumbersome.

If anyone has any suggestions, as to how we may be able to better deal
with this problem please advise me. I am also looking to see How I can
leave the data fields blank (for a tremplate) without getting the
#REF# in the formulas. This seems to happen even when the sheets are
locked and protected.

 

[Microsoft]

Thats nic, but how can I tell a client that I am billing them for
..0453453453 Minutes?

Whether it means a second, a minute or an hour I need these numbers to be
understandable

 

[Ron Rosenfeld]

Thats nic, but how can I tell a client that I am billing them for
.0453453453 Minutes?

Whether it means a second, a minute or an hour I need these numbers to be
understandable

I thought you wanted to bill in whole minutes? Why would you tell a client you
are billing them for .045 minutes? You should round that up to 1 minute.

You still have not described what you have termed "geek time". Nor have you
described what Excel is doing after you paste in the data, except to say that
Excel turns your data into "geek time", whatever that may be.

As I wrote before, it is not possible to give you an answer to all of your
questions without that information. And, if you cannot figure it out from my
description of how Excel stores times, and you won't tell us what it is doing,
it will be very difficult to help you. You need to be very specific.

For example, on my computer, when I paste in the example you give, the first
three times in colE get imported as text strings, and the last one as a time
string equal to 18 minutes. A straight conversion of the first three times
would result hours:minutes type of result. But I have no idea what is
happening on your computer, so a solution that works for mine may or may not
work on yours.

So, if you need further help, you will need to post back with precise data on
what is happening when you paste this data into Excel, and what you would like
to happen. You need to be specific, such as:

69:48 becomes 2.908333333 and I want it to be 69:48 or 69 minutes 48 seconds
rounded up to 70 minutes.

And so forth.

You could also post the results of your SUBSTITUTE formula.

If ALL of the COL E data is being imported as text in your example, then:

=CEILING(VALUE(SUBSTITUTE(E1,":",".")),1)

would give you a result in minutes, rounded up to the nearest 1. And this
would be stored as a decimal (integer) number, not as an Excel Time format. So
you would only bill in minutes and not, for example, in hours and minutes.

There are just so many possibilities ...



--ron