Formula for time difference

Hey... Its me again... with problems about time again...


What I am doing now is calculating time differences...
So...
1) Column A reflects the start time,
2) Column B reflects the end/stop time,
3) Column C reflects the time difference (B-A),
4) Column D reflects the target time.

What I wanted is that, if the difference is more than the target time, the cell would be colored red, which I succeed in doing through condition formatting.
On the other hand, if the target time is equal or larger than the time difference, the cell would be colored green, which I succeed in doing that too.

However, since "0:00" is for time difference is technically smaller than target time, the cell is colored green (which is not what I wanted).
Therefore, I was wondering if anyone can come up with a formula whereby if D>=C and that cell is more than 0, the cell would become green.


Short request:
Merge "D equal or more than C" and "C more than 0".

=AND(D>=C, C>0, C<>"")


Regards, TMS

So it's me again... that i have to ask for you to upload a small sample workbook as Times format is a little "strange" issue in Excel.


To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thanks guys!

TMS's solution works fine for me.
Though I only use the "=AND(D>=C, C>0)". Not sure what does "C<>" do.

Thanks Fotis too, though I don't need to upload a sample I guess. Will start a new thread if something happens again.

You're welcome.

It checks that the value isn't blank.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Originally Posted by MyOnion

Thanks Fotis too, though I don't need to upload a sample I guess..

I guess too!!

Thanks for the rep.