how can i create a formula or format the cells so the answer is a.

J

jenniss

I am creating a timetable in excel!

i wish to create a formula that shows a negative result so 7:24 - 06:00
would be - 1:24 not ##########

also if this is possible to do can the figure be in the format showing -
figure in red when negative and inshowing + figure in black to show positive?

does anyone know the answer?
 
F

Frans van Zelm

Hi Jennis,

Try setting Tools-Options-tab Calculation-option 1904 data system to ON.

To color negative red:
- Format-Cells-tab Number
- category Custom with code +hh:mm;[Red]-hh:mm

Give it a try, Frans
 
D

Dave O

How is your data presented on the spreadsheet? When I place 7:24 in
cell A1 and 6:00 in cell B1, the result of formula in cell C1, =A1-B1,
is 1:24. I'm not sure why you'd get the series of ########.

As for different fonts or colors when a number is negative, check out
Conditional Formatting. Place the cell pointer cell C1 (in this
example); on the menu click >Format >Conditional Formatting. In the
window, select "Cell value is" in the first box, "less than" in the
middle box, and enter 0 in the last box. Then press the format button
and select the format you prefer.
 
D

Dave O

How is your data presented on the spreadsheet? When I place 7:24 in
cell A1 and 6:00 in cell B1, the result of formula in cell C1, =A1-B1,
is 1:24. I'm not sure why you'd get the series of ########.

As for different fonts or colors when a number is negative, check out
Conditional Formatting. Place the cell pointer cell C1 (in this
example); on the menu click >Format >Conditional Formatting. In the
window, select "Cell value is" in the first box, "less than" in the
middle box, and enter 0 in the last box. Then press the format button
and select the format you prefer.
 
J

jenniss

thanks your a star you solved my problem, your pure geniuos

Frans van Zelm said:
Hi Jennis,

Try setting Tools-Options-tab Calculation-option 1904 data system to ON.

To color negative red:
- Format-Cells-tab Number
- category Custom with code +hh:mm;[Red]-hh:mm

Give it a try, Frans

jenniss said:
I am creating a timetable in excel!

i wish to create a formula that shows a negative result so 7:24 - 06:00
would be - 1:24 not ##########

also if this is possible to do can the figure be in the format showing -
figure in red when negative and inshowing + figure in black to show positive?

does anyone know the answer?
Click to expand...
Click to expand...
 
F

Frans van Zelm

Pff, this makes me blush.

I see that you are 'busy', regarding your other postings.

Frans.
 
Top