[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 said:
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 -----
.