Excel - formatting a cell number
Thread Starter
Pilots' Pal
Joined: Nov 1998
Posts: 1,158
Likes: 0
From: USA
Excel - formatting a cell number
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?
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?
Joined: Aug 2006
Posts: 80
Likes: 0
From: pub
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.
Joined: Aug 2005
Posts: 1,693
Likes: 0
From: fairly close to the colonial capitol
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......
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....
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......
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....
Joined: Dec 2003
Posts: 643
Likes: 30
From: Global Vagabond
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
http://www.cpearson.com/excel/datetime.htm
Last edited by mini; 20th October 2006 at 21:49. Reason: spelling (again...)
