data in X:XX format

[telefono]

I had previously made similar posts, including the one quoted below. It
seems after making posts here, I can never find them again. I found this on
google finally , and am reposting it here. I am frustrated with this problem
after three months of posts!

The problem is that my times are not in XX:XX format. They are often in X:XX
(Min and seconds)

There are examples in the original quoted message below. Dont flame and act
like im an idiot. I have worked on this for months and need a real solution
that I can understand! Not someone telling me I need to provide more
information! "GEEK" format is the manner in which microsoft automaticly
meddles with the data that is pasted, simply because it has a ":"! As it is
originally pasted it remains unchanged, however on the next sheet I want to
make it come out as a rounded up minute without manually entering any data.
And YES I DID GIVE AN EXAMPLE OF THE CHANGED DATA! .".. how can I tell a
client that I am billing them for
..0453453453 Minutes?..."

This seems like it should be easy for a program like excel take minutes and
seconds and round them up! Not so simple after all!

The example data below is still relevant however here is a new sample

Duration
2:21
0:43
1:19
0:58
2:49
0:10
10:02
13:24
4:15
2:44
1:11
0:08
10:15
0:08
3:19
5:45
3:14
1:05


Convert to the following with this formula "=SUBSTITUTE(Data!F2,":",".")" NO
MATTER WHAT I DO IN Excel 97!

Min Calc
0.0979166666666667
0.0298611111111111
0.0548611111111111
0.0402777777777778
0.117361111111111
0.00694444444444444
0.418055555555556
0.558333333333333
0.177083333333333
0.113888888888889
0.0493055555555556
0.00555555555555556
0.427083333333333
0.00555555555555556
0.138194444444444
0.239583333333333
0.134722222222222
0.0451388888888889


Using "=CEILING(A1,1/1440)" gives me a "0.00: result! from the "1:15"
example

If I change the cell formating of the in the first example to MM:SS format
the result Is that it changes all of the times! an example follows:
Duration
21:00
43:00
19:00
58:00
49:00
10:00
02:00
24:00
15:00
44:00
11:00
08:00
15:00
08:00
19:00
45:00
14:00
05:00
Apparently it does this as a result of the digits that represent minutes
being completely ignored!

as for this suggestion "=CEILING(VALUE(SUBSTITUTE(E1,":",".")),1)" that
yeilds " 1:05" turning into "00001"
Also,I have resolved the #REF# problems with a lot of "if,thens"


Mark de Leon M.



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.
Mensaje 2 de la secuencia
Autor:Ron Rosenfeld ([email protected])
Asunto:Re: Excel mm:ss strings and rounding up


View this article only
Grupos de noticias:microsoft.public.excel.worksheet.functions
Fecha:2003-07-01 13:04:10 PST

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

Well, you show what you paste, but you don't show who Excel is changing it.
Nor do you tell us what "geek" time is. That information would be useful.

Excel stores time as a fraction of a day. So one hour is stored as 1/24 =
0.041666667 and one minute is stored as 1/24/60 = 1/1440 = 0.000694444.

So if your data is stored similarly, to roundup to the nearest minute, you
could use a formula such as: =CEILING(A1,1/1440)

I can't help you with your pasting problems as you don't give enough
information.


--ron
Mensaje 3 de la secuencia
Autor:Microsoft ([email protected])
Asunto:Re: Excel mm:ss strings and rounding up


View this article only
Grupos de noticias:microsoft.public.excel.worksheet.functions
Fecha:2003-07-01 19:49:32 PST


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

Well, you show what you paste, but you don't show who Excel is changing it.
Nor do you tell us what "geek" time is. That information would be useful.

Excel stores time as a fraction of a day. So one hour is stored as 1/24 =
0.041666667 and one minute is stored as 1/24/60 = 1/1440 = 0.000694444.

So if your data is stored similarly, to roundup to the nearest minute, you
could use a formula such as: =CEILING(A1,1/1440)

I can't help you with your pasting problems as you don't give enough
information.


--ron

Mensaje 4 de la secuencia
Autor:Ron Rosenfeld ([email protected])
Asunto:Re: Excel mm:ss strings and rounding up


View this article only
Grupos de noticias:microsoft.public.excel.worksheet.functions
Fecha:2003-07-01 20:57:53 PST

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

 

[telefono]

I stand corrected the field I said was 0001 came out as 1 , but still from
1:05 it should be 2, not 1.

I had previously made similar posts, including the one quoted below. It
seems after making posts here, I can never find them again. I found this on
google finally , and am reposting it here. I am frustrated with this problem
after three months of posts!

The problem is that my times are not in XX:XX format. They are often in X:XX
(Min and seconds)

There are examples in the original quoted message below. Dont flame and act
like im an idiot. I have worked on this for months and need a real solution
that I can understand! Not someone telling me I need to provide more
information! "GEEK" format is the manner in which microsoft automaticly
meddles with the data that is pasted, simply because it has a ":"! As it is
originally pasted it remains unchanged, however on the next sheet I want to
make it come out as a rounded up minute without manually entering any data.
And YES I DID GIVE AN EXAMPLE OF THE CHANGED DATA! .".. how can I tell a
client that I am billing them for
.0453453453 Minutes?..."

This seems like it should be easy for a program like excel take minutes and
seconds and round them up! Not so simple after all!

The example data below is still relevant however here is a new sample

Duration
2:21
0:43
1:19
0:58
2:49
0:10
10:02
13:24
4:15
2:44
1:11
0:08
10:15
0:08
3:19
5:45
3:14
1:05


Convert to the following with this formula "=SUBSTITUTE(Data!F2,":",".")" NO
MATTER WHAT I DO IN Excel 97!

Min Calc
0.0979166666666667
0.0298611111111111
0.0548611111111111
0.0402777777777778
0.117361111111111
0.00694444444444444
0.418055555555556
0.558333333333333
0.177083333333333
0.113888888888889
0.0493055555555556
0.00555555555555556
0.427083333333333
0.00555555555555556
0.138194444444444
0.239583333333333
0.134722222222222
0.0451388888888889


Using "=CEILING(A1,1/1440)" gives me a "0.00: result! from the "1:15"
example

If I change the cell formating of the in the first example to MM:SS format
the result Is that it changes all of the times! an example follows:
Duration
21:00
43:00
19:00
58:00
49:00
10:00
02:00
24:00
15:00
44:00
11:00
08:00
15:00
08:00
19:00
45:00
14:00
05:00
Apparently it does this as a result of the digits that represent minutes
being completely ignored!

as for this suggestion "=CEILING(VALUE(SUBSTITUTE(E1,":",".")),1)" that
yeilds " 1:05" turning into "00001"
Also,I have resolved the #REF# problems with a lot of "if,thens"


Mark de Leon M.



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.
Mensaje 2 de la secuencia
Autor:Ron Rosenfeld ([email protected])
Asunto:Re: Excel mm:ss strings and rounding up


View this article only
Grupos de noticias:microsoft.public.excel.worksheet.functions
Fecha:2003-07-01 13:04:10 PST

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

Well, you show what you paste, but you don't show who Excel is changing it.
Nor do you tell us what "geek" time is. That information would be useful.

Excel stores time as a fraction of a day. So one hour is stored as 1/24 =
0.041666667 and one minute is stored as 1/24/60 = 1/1440 = 0.000694444.

So if your data is stored similarly, to roundup to the nearest minute, you
could use a formula such as: =CEILING(A1,1/1440)

I can't help you with your pasting problems as you don't give enough
information.


--ron
Mensaje 3 de la secuencia
Autor:Microsoft ([email protected])
Asunto:Re: Excel mm:ss strings and rounding up


View this article only
Grupos de noticias:microsoft.public.excel.worksheet.functions
Fecha:2003-07-01 19:49:32 PST


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

Well, you show what you paste, but you don't show who Excel is changing it.
Nor do you tell us what "geek" time is. That information would be useful.

Excel stores time as a fraction of a day. So one hour is stored as 1/24 =
0.041666667 and one minute is stored as 1/24/60 = 1/1440 = 0.000694444.

So if your data is stored similarly, to roundup to the nearest minute, you
could use a formula such as: =CEILING(A1,1/1440)

I can't help you with your pasting problems as you don't give enough
information.


--ron

Mensaje 4 de la secuencia
Autor:Ron Rosenfeld ([email protected])
Asunto:Re: Excel mm:ss strings and rounding up


View this article only
Grupos de noticias:microsoft.public.excel.worksheet.functions
Fecha:2003-07-01 20:57:53 PST

 

[Ron Rosenfeld]

I had previously made similar posts, including the one quoted below. It
seems after making posts here, I can never find them again. I found this on
google finally , and am reposting it here. I am frustrated with this problem
after three months of posts!

The problem is that my times are not in XX:XX format. They are often in X:XX
(Min and seconds)

There are examples in the original quoted message below. Dont flame and act
like im an idiot. I have worked on this for months and need a real solution
that I can understand! Not someone telling me I need to provide more
information!

Flaming your potential responders for not being able to read your mind is not
the best way to induce people to spend their time trying to explain things.

"GEEK" format is the manner in which microsoft automaticly
meddles with the data that is pasted, simply because it has a ":"! As it is
originally pasted it remains unchanged, however on the next sheet I want to
make it come out as a rounded up minute without manually entering any data.

Aha. That definition makes sense. What Excel is doing is interpreting the data
as hours:minutes rather than as you would prefer. It will do that with any
data inputted that way. It then stores the data as a fraction of a day and
displays it with whatever format you have selected.

So first you have to convert the data to minutes:seconds; then round it up to
the nearest minute; and then display it somehow.

Assume the time is in A1.

To convert it from hours:minutes to minutes:seconds divide by 60.
To round it up to the nearest minute, use the CEILING function. In the CEILING
function, "significance" (see HELP) is 1 minute. This can be expressed either
as 1/1440 (1440 minutes in a day), or as TIME(0,1,0).

So the formula to convert your 2:21 input into 3 minutes is\

=CEILING(A1/60,TIME(0,1,0)) which is the same as
=CEILING(A1/60,1/1440)

This result will be in what you call GEEK time. If you format it
(Format/Cells/Number/Custom Type: mm:ss) it will display as 03:00.

If you format it as General, however, it will display as 0.002083333.

==================

Now *you* have to decide whether you want to have the result in GEEK time, or
in decimal minutes.

If you want it in GEEK time, you already have it if you followed the above
directions.

If you want the result in decimal minutes, multiply the result of the above
formula by 1440 (number of minutes in a day) and format it as NUMBER with 0
decimal places (format/cell/number/ 0 decimal places.

Here are some equivalent formulas to do that:

=CEILING(A1/60,1/1440)*1440
=CEILING(A1/60,TIME(0,1,0))*1440

and my favorite, if you want decimal results:

=CEILING(A1*24,1)

=======================

HTH,

--ron

 

[Ron Rosenfeld]

=IF(SECOND(TIME(0,IF(MID(A1,2,1)=":",VALUE(LEFT(A1,1)),VALUE(LEFT(A1,2))),VA
LUE(RIGHT(A1,2))))=0,MINUTE(TIME(0,IF(MID(A1,2,1)=":",VALUE(LEFT(A1,1)),VALU
E(LEFT(A1,2))),VALUE(RIGHT(A1,2)))),MINUTE(TIME(0,IF(MID(A1,2,1)=":",VALUE(L
EFT(A1,1)),VALUE(LEFT(A1,2))),VALUE(RIGHT(A1,2))))+1)

Your formula gives incorrect results:

Data Mine Yours
2:21 3 2
0:43 1 1
1:19 2 1
0:58 1 2
2:49 3 1
0:10 1 1
10:02 11 1
13:24 14 1
4:15 5 1
2:44 3 2
1:11 2 1
0:08 1 1
10:15 11 1
0:08 1 1
3:19 4 1
5:45 6 1
3:14 4 1
1:05 2 2

I used =CEILING(A1*24,1)



--ron

 

[JON-JON]

This is because I presume the data given is in "text" not recognized by
excel as number. Try '2:21 (w/ apostrophe) the result will also be 3 and so
with the rest.

 

[Ron Rosenfeld]

This is because I presume the data given is in "text" not recognized by
excel as number. Try '2:21 (w/ apostrophe) the result will also be 3 and so
with the rest.

OK, I believe you, although I didn't try it.

I still think

=CEILING(A6*24,1)

is simpler, and it'll work with either 2:21 or '2:21


--ron

 

[Myrna Larson]

I expect your presumption is incorrect. When you type 3:30 into a cell, Excel interprets it as 3
hours and 30 minutes (not as text). The OP intends 3 minutes and 30 seconds.

 

[telefono]

There are no hours, always minutes and seconds

Thanks,

Mark

HI,

Assume your data is in A1 put this formula to B1
=IF(SECOND(TIME(0,IF(MID(A1,2,1)=":",VALUE(LEFT(A1,1)),VALUE(LEFT(A1,2))),VALUE(RIGHT(A1,2))))=0,MINUTE(TIME(0,IF(MID(A1,2,1)=":",VALUE(LEFT(A1,1)),VALUE(LEFT(A1,2))),VALUE(RIGHT(A1,2)))),MINUTE(TIME(0,IF(MID(A1,2,1)=":",VALUE(L
EFT(A1,1)),VALUE(LEFT(A1,2))),VALUE(RIGHT(A1,2))))+1)

But you can simplify this (to be easily understood) if you put a helper
column
Put this formula to B1
=TIME(0,IF(MID(A1,2,1)=":",VALUE(LEFT(A1,1)),VALUE(LEFT(A1,2))),VALUE(RIGHT(
A1,2)))
This will convert your data to time-format that excel can understand.
Format it in a custom format "mm:ss" (w/o the quote) and see the results

Now put this to C1 to get your desired result
=IF(SECOND(B1)=0,MINUTE(B1),MINUTE(B1)+1)

Though untested I am confident this will work to all your sample data. The
only problem I see is when you have durations that involves HOUR but from
here you can make a work around.

Hope this could help!

Jon-jon

I stand corrected the field I said was 0001 came out as 1 , but still from
1:05 it should be 2, not 1.

this
on in
X:XX and
act it
is want
to minutes
and "=SUBSTITUTE(Data!F2,":",".")" changing
it.

1/24

 

[telefono]

I almost thought this worked ron, But one problem is that Excel still thinks
its a time of day, and not minutes and seconds. When I enter 63:01 it
changes the original field to 15.01, This is part of the original problem.
There are two issues.
1) formatting of pasted data cells. (excel assumes the format is HH:MM:SS or
If i change it to MM:SS it incorrectly alters the data as I indicated below.

Thew second issue, is in the next field where I am trying to use the data it
appears as GEEK TIME format (Excel time format) . Now plkease dont act as if
you have no idea what I mean here!

 

[telefono]

I can not possible enter an apostrophy at the beginning of every time field!
Isnt this why we have programs to work with data like this to that that kind
of work for us? Oh, thats only when w foirst puut the data in microsoft's
language?

Mark

 

[Myrna Larson]

That's the root of your problem. Excel assumes a time with just two parts is hours and minutes,
not minutes and seconds.

 

[Myrna Larson]

Excel doesn't purport to work with every possible type of data. It does work with times, but the
assumption is that the time you enter is hours and minutes. This is the correct assumption for
the vast majority of cases, I'm sure. If you don't want to enter 0 for the hours, then you'll
have to adjust the value by dividing by 60 as part of your formula, as others have pointed out.

 

[Ron Rosenfeld]

I almost thought this worked ron, But one problem is that Excel still thinks
its a time of day, and not minutes and seconds. When I enter 63:01 it
changes the original field to 15.01, This is part of the original problem.
There are two issues.
1) formatting of pasted data cells. (excel assumes the format is HH:MM:SS or
If i change it to MM:SS it incorrectly alters the data as I indicated below.

It does work. The problem is that you did not use the formula I wrote; nor did
you apply the formatting I told you to.

=====================
Enter the 63:01 in A1.

In A2, enter the formula =CEILING(A1*24,1)

Then format A2 as Number with 0 decimals.
====================

I suspect you have a typo in the above and really mean 15:01 rather than 15.01.
If that is not the case, post back.

Please reread my post; apply the formulas; and format it as I instructed.

Thew second issue, is in the next field where I am trying to use the data it
appears as GEEK TIME format (Excel time format) . Now plkease dont act as if
you have no idea what I mean here!

Again, please reread my post and follow the instructions step-by-step.

You still have not said whether you want the result to be in GEEK time or
decimal time, but instructions for BOTH are in my post of 8:08 AM (EDT).



--ron

 

[telefono]

I have triede ever suggestion here beyond manually altering data! No
solution has yet worked. Can I at least force excel to see it as text,
without it converting it to decimal? It seems the colum properties change
when I paste in new data.

Excel doesn't purport to work with every possible type of data. It does work with times, but the
assumption is that the time you enter is hours and minutes. This is the correct assumption for
the vast majority of cases, I'm sure. If you don't want to enter 0 for the hours, then you'll
have to adjust the value by dividing by 60 as part of your formula, as others have pointed out.

 

[telefono]

First it does not work because when I change the colum format as tyou
suggested, my entries of 3:32 will change to 3:31:00 . and the entries of
63:00 remain unaffected!

To XL, time of day and elapsed time (minutes and seconds) are stored
identically, i.e., as fractional days. It's only when displayed that
the difference is made. If your original cell was formatted as
[hh]:mm (the brackets telling XL not to convert hours >24 to days),
then your entry of 63:01 would display as 63:01. Since you have it
set as h:mm (without brackets), the number of days (2) is omitted.

But the value (2.635416667) is retained

XL's input parser doesn't care about cell format. Instead, the
parser looks at the entry and processes the entry according to its
rules - e.g., one or two digits, followed by a colon, followed by
one or two digits is interpreted as hours and minutes and converted
to a double precision floating point number after dividing by 24.
Again, the format of the cell is irrelevant to the parser.

I'm not sure why something like Ron suggested doesn't work:

A1: 63:01
A2: =CEILING(A1/60, "00:01") ====> 1:04 (h:mm)

or, in integer minutes:

A2: =CEILING(A1*24,1) ===> 64

I almost thought this worked ron, But one problem is that Excel still thinks
its a time of day, and not minutes and seconds. When I enter 63:01 it
changes the original field to 15.01, This is part of the original problem.
There are two issues.
1) formatting of pasted data cells. (excel assumes the format is HH:MM:SS or
If i change it to MM:SS it incorrectly alters the data as I indicated below.

Thew second issue, is in the next field where I am trying to use the data it
appears as GEEK TIME format (Excel time format) . Now plkease dont act

as if

 

[Alan]

Hi All,

I have been reading this thread uncertain whether to laugh or cry!

Telefono:

Is this the problem that you face (I could be wrong, but I cannot see a
succint expression of the issue anywhere here):

1) You want to enter times, as minutes and seconds, into a cell (we
assume A1).

For example, 2 mins, 31 seconds will be typed in as 2:31 (four
characters, followed by the ENTER key).

2) You want another cell (assume B1) to return the rounded up number of
whole minutes that will be billed.

For our example here, 2 mins 31 seconds will be returned as 3 mins.

Is that it?

If so,

Assuming your excel setup is 'standard' and you have not changed the
formatting of a new workbook:

1) Open a new, blank worksheet

2) Type 2:31 and then press enter whilst still on cell A1

3) A1 now *displays* as 02:31

Note that the actual *value* in the cell is 0.10486111....

This is because Excel has interpreted your input as meaning 2 hours, 31
minutes (a little over 10% of a day)

4) Enter the following formula in cell B1:

=CEILING(A1*24,1)

This converts the days that Excel is seeing (0.10486111....) into
hours, and rounds up to the nearest hour.

5) Check that the format of cell B1 is "General" - it should already
be, but just check to be sure.

6) This displays as 3, and the actual value of cell B1 is also 3.

This is actualy a fudge of course since we have converted *as if* the
values entered were hours and minutes, rather than minutes and seconds,
but it probably doesn't matter to you.


Similarly, if you entered 65:01 into A1, then B1 would return 66 (which
is the right answer). However, A1 would *display* 65:01 as 17:01.

If you want to correct for that, then apply the following format to
A1:

[hh]:mm


Does that do what you need?

If not, please post back, outlining where my description of your
problem is wrong.

Also, if you prefer to do it 'properly' (without the fudge of confusing
'hours and minutes' with 'minutes and seconds', then post back, and
I'll give those formulae too.

Alan.

 

[Hasen]

I have figured out your format and rounding problem. I did not have the
time to read the whole 2 pages of postings. I just read the main
posting and the issue described in it. if the posting has been going
on for 2 pages, I guess the riddle still hasen't been solved.

If you need help, email me.

hasen

 

[telefono]

Alan,

That is close to what I need, however yoyur proposed suggestion will not
work on a call of say 63 minutes in length!

 

[telefono]

My source for the data is HTML that is pasted into a page.

I think event macros may be it, or VB control settings per sheet that make a
MANDATORRY text formatting,however I have used excel very little, and do not
profess to be an expert. That is all beyond me.


Thanks,. Mark

 

[Alan]

Hi Telefono,

*Alan,

That is close to what I need, however yoyur proposed suggestion will
not
work on a call of say 63 minutes in length!

*

I think you'll find I covered that case in my reply above:

*

Similarly, if you entered 65:01 into A1, then B1 would return 66 (which
is the right answer). However, A1 would *display* 65:01 as 17:01.

If you want to correct for that, then apply the following format to
A1:

[hh]:mm

*

Specifically, in what way does that not work for a call of 63 mins in
length?

Thanks,

Alan.