Hello All,
I have a column of data which contains dates, and I want to make sure that all the dates are entered in the following format 20/01/2005 and I’m wondering whether conditional formatting can be applied to make sure that all the dates in this column are correct. If a incorrect format is entered I would like the cell to be highlighted in red. Can anyone help me please, I am using Excel 2003,thanks?
Hawkmoth1
To check if they are dates, select the column and then format the cells as General. Any that still look like dates, aren't dates. Any that change into a five digit number are dates.
But why not use Data | Data Validation | Allow : Date to ensure that only acceptable dates are input?
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
To Excel, a date is just a number, and it is the formatting that you apply to the cell that determines whether it is shown as d/mm/yyyy or dd/mm/yyyy or even d/m/yy. Conditional formatting could not be used here.
Pete
You need to understand this about dates...a date is just a number indicating how many days have passed since 1/1/1900, so 03/22/15 is actually 42085. excel formats it into something that we recognise as a date
So, having said that, you should be able to see that the format that is set for a date, its really only a cosmetic setting - excel works on data, not cosmetics
Are you worried that someone could enter a date as 10/12/2015 meaning Oct 12, instaed of Dec 10?
can you show a few samples of what you concern is?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
To re-iterate what I said, and build on Pete and Ford's comments, changing the format of the cells will highlight any genuine date errors. Acceptable dates will change to numbers, badly input or formatted "dates" will still look like dates but are actually text values.
As has been explained, the formatting only changes what the date looks like, not the underlying value (the number of days from 1 January 1900)
Using Data Validation could prevent badly formatted or structured dates being entered.
Regards, TMS
Many thanks to everyone for your comments.
I can't set the cell format to the specific date type I want, as if I do this it makes certain assumption when a date is entered, eg, if a date of 'June 05' is entered the cell formatting changes the date to 01/06/05, but I might not want that specific date entered; therefore I decided to set the cell formatting in this column to 'text', therefore whatever is entered into each cell stays as exactly as entered. This is fine. but I need a way to highlight any entry into this column which doesn't match the format of, eg, 13/09/2013. I hope this is a little more clear.
Hawkmoth1
Perhaps do a test for LEN(cell-ref) <>10 ...this woukd be for dates in the format of dd/mm/yyyy. You would need to adjust the test length (10) to cater for shorter "dates"
Hello FDibbins,
Many thanks for giving me a solution to my date formatting issue, which, while not 100% failsafe, it is a very good workaround for me.
Hawkmoth1
Happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks