jens formula highlight

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Jens - a formula to highlight incorrect data (dates)

Jens - a formula to highlight incorrect data (dates)

resolvedResolved · High Priority · Version 365

Adam has attended:
Excel Pivot Tables course

Jens - a formula to highlight incorrect data (dates)

Hello!

So here is a question: I receive documents with data about incidents that I transfer onto an excel database. Within this spreadsheet, I have to separate columns with the following titles:

1. ROW F: The date the document was received (ie 09/07/24)
2. ROW G: The date the incident occurred (ie 08/06/24)

Logically, the date in Row F should always surpass the date in row G I have noticed however, that due to human error, some of the data in these columns are the wrong way around - For instance somebody has put the date of the incident in the column where it is supposed to have the date the document was received, meaning there are some wrong rows where it looks like the incident occurred AFTER the document was received.

This can be an easy fix to find manually but I now have over 1000 cases to check to make sure the data has been coded correctly. Is there an easy way to identify this? I was thinking - Is there a formula that can be applied to Row F which highlights the cell red when the date in ROW G surpasses the date in Row F (or vice versa)?

Hope that makes sense, look forward to hearing from you soon!

Adam

RE: Jens - a formula to highlight incorrect data (dates)

Hi Adam,

Thank you for your forum question.

You would need to create a formula within Conditional Formatting > New Rule. Let's say your actual dates started in row 2. Here's how to do it:

1. Select all the incident dates in the G column starting at G2

2. Go to Conditional Formatting > New Rule

3. Select 'Use a formula to determine which cells to format'

4. In the field below, type =$G2>$F2

5. Select the 'Format' button, pick a red colour and click OK twice to apply the formatting

I hope this works on your data. Please let me know if it does

Kind regards
Martin Sutherland
(IT Trainer)


 

Excel tip:

Select blank cells automatically

Get Excel to find any blank (empty) cells in a region for you by:

1. Selecting the appropriate region from your spreadsheet.

2. On the menu bar, go to Edit - Go to.

3. Click the 'Special' button, then select Blanks and click OK.

View all Excel hints and tips


Server loaded in 0.09 secs.