Excel - formatting a cell number

[Bus429]

I am using Excel to monitor an aircraft's modification status, maintenance and hours. I want to record aircraft hours in hh:mm and link it to other cells to show time run/remaining etc.
I have tried the options available in Excel but cannot get (for example) a current hours of 8000:00 against a Fatigue Life Limit of 12000:00 to get a cell to show the difference (4000 hours); all I get is "value"

Any ideas?

[W.R.A.I.T.H]

Excel is an a$$, I've spent like decades with it lately trying to persuade it to use angular format for some geodetic fun, and nearly lost my hair. I even designed a new format in VBA there, which for me is a feat unheard of, but it kept on giving trouble anyway. What I would do, were I brighter, would be to define my own format in VBA. But since I'm not, I just transform everything to decimal system, do calcs therein and transform it back to hour format at output.:ok:

[Sleeping]

If you format the cells as "number / custom" [hh]:mm and then insert a function into a third cell eg =SUM(A1-A2) you should be able to subtract one cell value from another.

Works for me anyway!:ok:

zzzzzzzzzz

[Bus429]

Sleeping - tried it - no good

[vapilot2004]

Try Sleeping's formatting trick again, Bus and type the format as such:

Select all cells involved in input and results, then click Format > Cells (or CTL + 1) and click Custom then in the Type Box enter:

[hhhh]:mm then click OK.

hhhh works up to 4 digits, naturally - type more if you need above 9999 hours.

Highlight the desired results cell then goto the Formula bar and type an = sign then highlight your high value cell then enter in the Formula bar the - sign then highlight your low cell value then hit the enter key.


Worked here on Excel 2002 XP and Excel 2000. Note: Ignore Excel's reference in the fx bar to strange dates when data input cells are selected - happens anytime we use colon(s) with numbers.

W.R. has it right, I would only add all MS Office Apps are all ar$es in that they often give us the illusion of flexibility and ease of use but...... :ugh:

You can always record the hours and minutes in their own cells and with some creative cyphering applied, you may have the desired results at hand.... :ugh:

[Sleeping]

Hmmmm, agree with vapilot2004. Definately works for me - using Excel 2002 XP.
:ugh:

[mini]

This seems to be a common problem, I usually take the decimal route with conversion to and from. Just had a quick search around and this guys' solutions look interesting:

http://www.cpearson.com/excel/datetime.htm