data in X:XX format

[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!

The threading in the group seems to have gotten out of sync - probably my
fault for using a web-board to reply.

Anyhow, please see my reply to your query above here: (two links, hopefully
one will work):




Also, copied here:

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

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.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

 

[telefono]

Alan, thanks for yur reply, however your links yielded no results , just
unable to find server message. Anyway, I believe what you did propos was
interpreting M:SS as H:MM , the problem with that is a time of 32:00 (over
24:00 or 12:00 does not seem to work, as its based on a 24 hour day

Mark

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!

The threading in the group seems to have gotten out of sync - probably my
fault for using a web-board to reply.

Anyhow, please see my reply to your query above here: (two links, hopefully
one will work):

..com

Also, copied here:

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

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.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

 

[Alan]

Hi Mark,

You are right that using hours and mins, rather than mins and seconds
leads to problems over 24 hours.

That is what I meant when I pointed out that even though you enter,
say, 63:01 in a cell, it might *display* as 15:01.

That does not change the underlying value though, which will be
2.62569444....

However, as I mentioned, if you change the *format* to:

[hh]:mm

then it will display as 63:01.


You also point out that we are 'pretending' that mins and secs are
hours and mins. You are right, but since the relationship between mins
and seconds is equivalent to that between hours and mins (the factor of
60), it doesn't actually matter does it?

The only point that it *would* matter, will be if you want or need to
start getting into fraction of seconds or, at the other end, into
hours.

As long as all your units are mins and seconds, you don't have to
worry. Since you have mentioned durations of more than 60 mins (63
mins, 1 second for example), I am guesing that you want to continue to
measure in minutes, and not convert to hours?

Is that correct? If not, we need to re-consider.

If on the other hand you always bill for a whole number of minutes (3
mins, 27 mins, 63 mins, 487 mins for example) then there is no issue.

Am I missing the point here?

Hope that helps a little more!

Alan.

 

[telefono]

The point is that one can not simply reformat on a cell by cll basis to
accomodate whether the time is 63:01 or 1:24. That is the first problem. The
second problem is once the cell formatting is changed, the times often
become decimals and the original entries are unrecoverable. I cant nbill
someone for 15 min for a all that was actually 63. I dont want to have to
manually fudge every entry, it would be easier to round up manually and make
whole inute entries! That is why Excel was created was to perform tasks
like this. I cant and dont believe that Microsoft was so short sighted that
I can not even preformat those cells as text, unconditionally, so I can deal
with it my own way.

THe point is the end result MUST be understandable in the spreadshet and to
the client receiving the bill. If I billed you for 63 min and it showed up
as 15 on you bill, you might argue the billing cost, right?