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

[Guest]

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?

 

[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

 

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

 

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

 

[Guest]

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

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

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?

 

[Frans van Zelm]

Pff, this makes me blush.

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

Frans.