I'm having problems finding the difference between to columns of time
formatted into mm:ss. The difference shows up as ##### So, I converted each
column of mm:ss into numbers with this formula: =TIMEVALUE(TEXT(I3,"hh:mm"))
such that 03:00 = 0.002083333, 02:04 = 0.086111111 the difference =
0.088194444 using format custom mm:ss to reconvert back to time is
07:00. Now I'm stuck just not sure which function I should use to correctly
reconvert the difference back into "mm:ss" Any help is greatly
appreciated.

Thanks EVan

Just corrected a mistake, function is now =TIMEVALUE(TEXT(I3, "mm:ss")) such
that 3:00 = .125, 02:04 = .166666667 the difference = -0.41666667. So I have
a new problem a negative number. Is this more easily solved by converting
all time into seconds using 86400? Then my problem is how to convert the
seconds back to a mm:ss format. Already trid to format the column using
Format Custom mm:ss but it doesn't work.

"Evan" wrote:

Excel doesn't play nice with negative times unless you change a setting.

Tools|Options|Calculation tab|Check 1904 date system

Be aware that your existing dates will be now be off by 4 years and one day.
You can fix this, though. But a bigger problem may be when you copy dates from
one workbook to another (each using different date systems).

Saved from a previous post:

Be aware that any existing date will now be off by 4 years and 1 day. And
copying dates between workbooks becomes a problem, too.

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

======
Another option would be to use a formula that returned text (no longer a
number/time):

=if(i3<j3,"-","")&text(abs(i3-j3),"hh:mm")

Where i3 and j3 are real times.


Evan wrote:

Just corrected a mistake, function is now =TIMEVALUE(TEXT(I3, "mm:ss")) such
that 3:00 = .125, 02:04 = .166666667 the difference = -0.41666667. So I have
a new problem a negative number. Is this more easily solved by converting
all time into seconds using 86400? Then my problem is how to convert the
seconds back to a mm:ss format. Already trid to format the column using
Format Custom mm:ss but it doesn't work.

"Evan" wrote:

--

Dave Peterson

Not sure what you're trying to do here.

The difference shows up as #####

Those show up when you try to subtract formatted time values and the result
is a negative value. Using the default date setting Excel won't display
negative dates/times and it gives you those lovely hash marks.

03:00 = 0.002083333
02:04 = 0.086111111

The 02:04 is incorrect. 2 minutes 4 seconds is 0.00143518518518519

0.0861111111111111 is 2 hours 4 minutes

When you enter a time you must enter it with the hour.

0:3:00
0:2:04

You can format the cell to not display the hour so that it appears as:

3:00
2:04

--
Biff
Microsoft Excel MVP


"Evan" wrote in message
...
I'm having problems finding the difference between to columns of time
formatted into mm:ss. The difference shows up as ##### So, I converted
each
column of mm:ss into numbers with this formula:
=TIMEVALUE(TEXT(I3,"hh:mm"))
such that 03:00 = 0.002083333, 02:04 = 0.086111111 the difference =
0.088194444 using format custom mm:ss to reconvert back to time is
07:00. Now I'm stuck just not sure which function I should use to
correctly
reconvert the difference back into "mm:ss" Any help is greatly
appreciated.

Thanks EVan

"Evan" wrote:
Just corrected a mistake, function is now =TIMEVALUE(TEXT(I3, "mm:ss"))

Yes, I noted a number of iniconsistencies in your original posting. But....


such that 3:00 = .125, 02:04 = .166666667 the difference = -0.41666667.

0.125 is 3h 0m -- 03:00 in "hh:mm" format -- and 0.166... is 4h 0m, nothing
like 2:04 in any format.

Moreover, 0.125 - 0.166... is -0.04166..., not -0.4166... .


So I have a new problem a negative number.

You have a problem with presenting your problem reliably.

If you have a question about a formula you are using, it would be prudent to
show the formula and all relevant values, using copy-and-paste, as well as
the result you want to see. In your case, it might be important to present
time values both as they appear and in Number format with at least 6 decimal
places.


Is this more easily solved by converting all time into seconds using
86400?

Without knowing what you are trying to do, it is hard to say. For the most
part, that is not likely to solve your problem. However, it might improve
the accuracy of your result.


----- original message -----

"Evan" wrote in message
...
Just corrected a mistake, function is now =TIMEVALUE(TEXT(I3, "mm:ss"))
such
that 3:00 = .125, 02:04 = .166666667 the difference = -0.41666667. So I
have
a new problem a negative number. Is this more easily solved by converting
all time into seconds using 86400? Then my problem is how to convert the
seconds back to a mm:ss format. Already trid to format the column using
Format Custom mm:ss but it doesn't work.

"Evan" wrote:

[PS: You posted an update with some material changes. However, since there
are as many inconsistencies in the update as in the original, and since the
original has more information, I will post my response to the original
message.]

"Evan" wrote:
I'm having problems finding the difference between to columns of time
formatted into mm:ss. The difference shows up as #####

That usually means that the difference is negative time. But it could also
mean that your column is not wide enough (unlikely!).

If you have a question about a formula you are using, it would be prudent to
post the formula along with any other relevant facts, for example the value
in all cells referred to in the formula. Otherwise, we must resort to
guessing.

My first guess....

The "time" in each cell is actually a date and time. For example, you
computed =A1-A2, where A1 is something like 1/1/2009 2:04, and A2 is
1/2/2009 0:03. Note that even though the time in A1 is greater than the
time in A2, the date is not; so the difference results in a negative date.

You can subtract just the time factors using =MOD(A1,1)-MOD(A2,1), formatted
as "hh:mm". However, that may or may not result in the correct result,
depending on your intent. You might need to compensate for the difference
in days, too.

But then I noticed some inconsistencies in your information. That leads to
another guess....


So, I converted each column of mm:ss into numbers with this formula:
=TIMEVALUE(TEXT(I3,"hh:mm"))

That is another way -- arguably a more reliable way -- to do MOD(I3,1), if
my assumption is correct. Otherwise, it is a superfluous step if my
assumption is wrong and you truly have simply time in each cell.

Aside.... You could simply do =--TEXT(I3,"hh:mm") and format using "hh:mm".


such that 03:00 = 0.002083333, 02:04 = 0.086111111

Note that 0.0020833... is 3m 0s, not 3h 0m. So it is 03:00 only if
formatted as "mm:ss" instead of "hh:mm".

But 0.08611... is 2h 4m, which is 02:04 if formatted as "hh:mm".

New guess: you have mixed formats, and your expectations are set
incorrectly based on the displayed values.

[PS: On the other hand, there are so many inconsistency in the data entered
in both your original and updated posting, it is probably foolish of me to
try to draw any conclusion from what you type here.]


the difference = 0.088194444 using format custom mm:ss to
reconvert back to time is 07:00.

0.0881944... is 2h 7m. That is 02:07 when formatted as "hh:mm". It does
appear 07:00 when formatted as "mm:ss".

In any case, that is the sum, not the difference, of the two times values
that you presented above.


Now I'm stuck just not sure which function I should use to correctly
reconvert the difference back into "mm:ss"

Hopefully, the above helps you.

Either enter just time, not date and time, or use MOD or alternatives to
extract just time.

Subtract the cell references straight-forwardly, or factor in the difference
in days, if the date component is significant to your calculation (e.g.
workshift times).

Alternatively, if negative time is desirable, post back here for suggestions
on how to present it.

Unless you post the actual formulas and cell values, using copy-and-paste
since your ability to re-enter the information here is obviously unreliable,
I cannot offer any further assistance.


----- original message -----

"Evan" wrote in message
...
I'm having problems finding the difference between to columns of time
formatted into mm:ss. The difference shows up as ##### So, I converted
each
column of mm:ss into numbers with this formula:
=TIMEVALUE(TEXT(I3,"hh:mm"))
such that 03:00 = 0.002083333, 02:04 = 0.086111111 the difference =
0.088194444 using format custom mm:ss to reconvert back to time is
07:00. Now I'm stuck just not sure which function I should use to
correctly
reconvert the difference back into "mm:ss" Any help is greatly
appreciated.

Thanks EVan

Thanks so much everyone for your responses. I actually found the answer how
to convert seconds to "mm:ss" format. It works so beautifully that I want to
share,
Please see:
The problem you are experiencing is A1/24 results in hours. Likewise,
A1/24/60 results in hours, and A1/24/60/60 results in seconds. Therefore, you
want the following function:

=TEXT(A1/24/60/60, "mm:ss")


http://ask.metafilter.com/35607/Very...ion-ever-asked

Gornish helfen, as my grandmother used to say :-).


----- original message -----

"Evan" wrote in message
...
Thanks so much everyone for your responses. I actually found the answer
how
to convert seconds to "mm:ss" format. It works so beautifully that I want
to
share,
Please see:
The problem you are experiencing is A1/24 results in hours. Likewise,
A1/24/60 results in hours, and A1/24/60/60 results in seconds. Therefore,
you
want the following function:

=TEXT(A1/24/60/60, "mm:ss")


http://ask.metafilter.com/35607/Very...ion-ever-asked