I am creating a sign in/out system in Excel. I was able to get the sign in and out system to work, however now I need to highlight rows that fit a certain formula.

I have tried this formula:

=IF(ISBLANK($N$2)AND($P$2>=$K2+$M2,$P$2<>"",$K2<>"",$M2<>""))

If the rows match these criteria, they will be formatted with a red background color.

I can provide the excel sheet if needed.

The basic info is:

Soldier A, B and or C all sign out together. They fill out a userform with required data, and once they save the form, it transfers their information to the next available row.

What needs to happen is, if the Sign In time column in each row is empty and the Sign Out time PLUS the Allotted time (sort of like a time limit) is <= NOW(), then the row should be formatted.

So far, I have been unable to accomplish this.

  • 2
    AND doesn't work that way, try: =IF(AND(ISBLANK($N$2),$P$2>=$K2+$M2,$P$2<>"",$K2<>"",$M2<>"")) – cybernetic.nomad Apr 3 at 14:35
  • 1
    @cybernetic.nomad the IF can be skipped altogether: AND(ISBLANK($N$2),$P$2>=$K2+$M2,$P$2<>"",$K2<>"",$M2<>"") – user313811 Apr 3 at 16:27
  • Inserting a formula in a conditional format is a bold move, because it doesn't show syntax errors. You should first test the formula in a cell, to see if the result is what you expected. Then, when you're sure it works, copy/paste the formula of the first cell into the conditional formatting, and remove the column. The formula provided by Mahesh should work, provided the $ are placed correctly. – piko Apr 4 at 10:38

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.