excel format - mind boggling

[stoke]

at least to me anyway.....
i have a table of calculating hours, the results are all correct and formatted the same (hh:mm)
-09:40
00:05
01:20
-16:25

i use this formula:
=CONCATENATE(A17," Variance"," is ",E17)

where A is a descriptor for that which the hours apply to (route 1) and E is the hours and minutes.

this works perfectly until the last entry which has the hh:mm showing as a decimal - despite the fact that at each point the formatting is the same.

i have copied the formatting, used format painter, dragged and copied the formauls and i cannot make the last result show as hh:mm.

The cell that feeds the last entry is formatted the same as previous entires and works and shows exactly as it should.

any tips would be very much appreciated!

Thanks,

 

[Zathras]

Not sure how you're getting the negative time value to format correctly. It doesn't work in Excel 97.

In any case, here is a formula you should be able to live with (assuming the values you are comparing are in columns D and E):
[blue]

  =CONCATENATE(A17,"  Variance  is  ",IF(D17>E17," ","-"),TEXT(ABS(D17-E17),"hh:mm"))

[/color]
Here is an alternate form using ampersands instead of CONCATENATE:
[blue]

  =A17&"  Variance  is  "&IF(D17>E17," ","-")&TEXT(ABS(D17-E17),"hh:mm")

[/color]

 

[stoke]

Thanks for your quick response.
I'm not comparing E17 to anything, i just need tobe able to use the value in that cell.

The thing that is really perplexing me is how it works for all the cells and refernces but onot for this particular one!

with regard to your comment about getting the negative vale to format correctly this is the formula i have for that -
=IF((G40-G8)<0,&quot;-&quot;&TEXT((-1*(G40-G8)),&quot;[hh]:mm&quot,TEXT((G40-G8),&quot;[hh]:mm&quot)

i hope i have explained myself, would it helpif you saw the actual worksheet?
i knowthis is cheeky, but if you are happy to helpthen this will probably resolve this query much sooner!

Thanks Zathras!

 

[stoke]

Thanks Zathras - but i have used my own formula above to solve this situation - ddin't think of it using it for a while!

still have no idea why the original situation came about though!

appreciate your help,