Highlight cell if format is wrong

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Howdy,

We need in column E (from E2 to E100), data entered from user, to be in this format only!!!
dd.mm.yyyy h:mm AM/PM
If data is in incorect format or in other format (say mm.dd.yyyy h:mm AM/PM or mm.dd.yyyy h:mm or ....) then highlight that cell or color font....

Thank you.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Are thy making text entries, or valid time/date entries?

If you are unsure, what does this formula return (for an entry in cell E2):
=ISNUMBER(E2)

All valid date/time entries are actually stored as numbers in Excel (the number of days since 1/0/1900, and time is just the fraction of one day). So that would return TRUE.
Text entries would cause that formula to return FALSE.
 

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for replay Joe,

The data are entered manually, so they can be numbers but also text.
We need somehow to show them (highlight cell) the date entered is not in correct format.
 

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Joe,

I studied, before posting, including the site to which you put that link.
I can't use something like that, because the data entered by users are from different years and formatted differently.
That is why we need the data entered to be strictly in this format:
dd.mm.yyyy h: mm AM / PM.
If it is not possible to highlight - in some way - in the worksheet, then maybe you can help me with a VBA code (possibly a userform)
Thank you.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Just to be clear,
Are you trying to prevent users from making bad entries?
- or -
Are you trying to verify existing data after the fact?

If dealing with existing data, can you insert a column and enter the following formula and copy it down for all rows:
=ISNUMBER(E2)
and let us know if you have a mixture of TRUE and FALSE values, or if they are all one or the other.

It would also be really helpful if you could post a sample of the different kind of entries, so we can see exactly what the data we are working with looks like.
 

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Just to be clear,
Are you trying to prevent users from making bad entries? YES
- or -
Are you trying to verify existing data after the fact? NO
..........
It would also be really helpful if you could post a sample of the different kind of entries, so we can see exactly what the data we are working with looks like.
I don't know how user will write data in excel file.

There is a warning - in a cell - how the data should be entered (dd.mm.yyyy h: mm AM / PM), but if the cell does not show in some way that the DATA was entered incorrectly, it will leave cell in that way.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
There is a warning - in a cell - how the data should be entered (dd.mm.yyyy h: mm AM / PM),
Where is this warning coming from?
Data Validation or VBA code? How exactly have you set it up? If Data Validation, did you choose the "Warning" option, or "Stop" option?

Here is probably what I would do:
1. Set the Custom Format that I want on the column
2. Use Data Validation to set your date range, and choose the "Stop" option to prevent anything else from being entered.

If that is not enough, we can also use VBA, specifically a Worksheet_Change event procedure which runs automatically as data is entered.
 

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Where is this warning coming from?

That message is written in a cell, written by me.

Here is probably what I would do:
1. Set the Custom Format that I want on the column
2. Use Data Validation to set your date range, and choose the "Stop" option to prevent anything else from being entered.

How can I set date range? (data entered could be 01.07.2000 6:35 AM, or 12.10.1978 11:45 PM, or 13.09.2012 11:17 AM....)

If that is not enough, we can also use VBA, specifically a Worksheet_Change event procedure which runs automatically as data is entered.

It's OK with worksheet change event
Thank you.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
How can I set date range? (data entered could be 01.07.2000 6:35 AM, or 12.10.1978 11:45 PM, or 13.09.2012 11:17 AM....)
I just want to know what the minimum and maximum date values that you want to allow them to enter is.
For the sake of this example, let's just say it is 1/1/1990 to 1/1/2030.

Assuming that you want to apply this on the range E2:E100, do the following.
1. Select the range E2:E100
2. Right-click on any of those selected cells and "Format Cells"
3. On the "Number" tab, select "Custom"
4. Enter this is the "type" box: dd.mm.yyyy h:mm AM/PM and click "OK"
5. Make sure the range E2:E100 is still selected
6. Go to the "Data" menu, and on the "Data Tools" ribbon, select "Data Validation"
7. On the "Settings" tab, set these values:
- Allow: Date
- Data: between
- Start date: 1/1/1990
- End date: 1/1/2030

8. Go to the "Input Message" tab, and enter any instructions/message you would like them to see
9. Go to the "Error Alert" tab, and set these values:
- Style: Stop
- Title: whatever you would like to be
- Error message: whatever you would like it to say when they enter bad data
10. Click "OK"

Now, try it out. It should do what you want. It will force them to enter a valid date/time between those date ranges.

If this suffices, then we do not really need VBA. You would just invoke VBA if you were worried about them removing Data Validation or changing the format of the cells (but then again if they were intent on doing that, they could just disable VBA code too, and that wouldn't run).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top