Minus Time Values / conditional formatting result

[martyp]

Hi everyone,

I just got lucky and got a job on a game show but my excel skills are stale and are failing me....

I have 2 problems:

1. I have a value in D19 that represents the ideal content time 38:27 formatted as mm:ss. I would like to compare the value of actual show time in D15 to this and find out if the show if under or over time with the result formatted at 01:10 for example for 1 min 10 secs over or -00:23 for example for 23 under ideal time. Right now when I do D15-D19 I get -59:59 representing a one second difference. I've been playing with this for an hour and feeling kinda stupid as I can't get it to work as I want. I have switch date format to the 1904 format.... Any thoughts would be greatly appreciated.

2. I'm using excel for mac 2011 and conditionally formatting seems to have changed a bit. I would like to set it so that if the value of a cell = 00:00 then it is highlighted in Green. If the value is other than this is should display the value highlighted in red. Possible?

Thanks,
marty

[FDibbins]

Ecxel doesnt like negative time, so try this instead...

=IF(B1>A1,B1-A1,A1-B1)

Then use CF....
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select "format cells that contain"
4. use "not equal to" and enter 0 format fill RED

[martyp]

THANK YOU!

The conditional formatting worked perfect and the if statement seems to make a lot of sense (I would have never thought of that) but does not deliver the result I'm looking for. If something is off 1 second it delivers a result of either 59:59 or -59:59. I think maybe I have the cells formatted wrong. I'm using mm:ss. I'll try to attach the workbook, it does not get any simpler... kinda of embarrassing... Again, thanks!

[FDibbins]

The problem is that your times are not actually times. Excel sees time as a fraction/decimal of an day/24,
So 06:00am is actually 0.25, 06:00 PM (18:00) is 0.75.

To fix this, on sheet2 B4, copied down, use this...
=TIME(0,E4,(E4-INT(E4))*60)

[martyp]

Thank you! Have a great weekend!

[FDibbins]

Happy to help