Time Format Change

[wsilen]

The to data exported for the result that I'm looking for is provided in 2 diffent formats. Example. data A is shown like: [05/30/2011 7:00:00am] and data B is shown like: [7:04:32am] the result that I'm looking for is the time difference between both time (4:32 min) but instead I get a result like 23:55:28.

Question is there any way that one of the 2 datas format can be change authomatically (instead of manually one by one) so it can provide the correct answer?

 

[jeffreybrown]

You're welcome, but I wouldn't go that far...I get lucky every once in awhile

 

[wsilen]

Good Afternoon.

How can I set a conditional Format that any result between (+) 0:05:00 and (-)0:05:00 is change to color green and any number higher than (+) 05:05:00 or below (-) 0:05:00 is RED

 

[shg]

You can't have negative time values in Excel unless you switch to the 1904 date system (which is a workbook-level option). If you do that, all your manual date entries will change.

If you want to do that, then you can skip Jeff's text formula and revert to =C4-D4, format the cell as green, and use one conditional formatting formula:

=abs(Me) > --"5:00" and format as red, where Me is the address of the cell where you are applying the format.

If you don't want to do that, then use Jeff's formula, format the cell as green, and use

=abs(c4-d4) > 5/24 and format as red.

 

[jeffreybrown]

Thank you shg for the assist