Period Separeted Dates

[JoeyGirl]

Hi Everyone,

I've browsed around the internet for a while now and I haven't been able to find much in-excel help on this (rather the general solution is to change the windows operating system date formats, which isn't possible given this spreadsheet will be used by many people on many different PC's).

What I am trying to do is make an existing spreadsheet more user friendly as there have been a number of calculation errors due to incorrect data input, and one major part of that is allowing for the different ways people enter dates. Some users insist on separating their dates with 'fullstops' and they seem to have great difficulty in changing the habbit so I'd like to know if there is a way to code/format around it?

In my relatively limited excel coding experience I'm thinking perhaps some sheet code on event of pressing enter in cells from a certain range all periods will change to slashes (and then if i have these cells formatted as date I will be able to calculate on them in the rest of the workbook).

Many thanks in advance for any help/advice you can give on this!

[JBeaucaire]

You could apply data validation to those cells that require the user to input SOMETHING Excel can interpret as a date value properly, else it refuses the input. This puts the onus back on the users.

[JoeyGirl]

Hi!

Yeah that's what I've set it up as at the moment, and it's good but it's definately not the best possible solution as they're entering quite a few dates in the workbook so the error messages really slow them down. I also worked on a Calendar userform but that will end up even slower again.

[JoeyGirl]

Any other ideas?

[JBeaucaire]

What I am trying to do is make an existing spreadsheet more user friendly as there have been a number of calculation errors due to incorrect data input...

As both the Excel programmer and the software trainer for my company I battle these same issues from time to time. People frequently think that adding programming into a workbook is the solution for their bad habits so bad habits can be "lived with". Of course that's true on the surface, but ultimately teaches users the exact opposite of what you want...

The BEST thing to teach user's is to enter data correctly and to specified parameters, The only reason "the error messages really slow them down" is because they keep making the same mistake over and over. I'm going to offer that they aren't making the mistake over and over, just occasionally and are simply bugged at not being free to type whatever they want.

I'll give you the solution you want, but I promise that you are laying the groundwork for them thinking they can "really" do anything they want and you'll fix it, and that's not a respectful attitude, but it's your garden to attend. Just remember my warning.

My best solution would be to simply answer this when they complain, "Yes, it does properly warn you to fix your date formatting errors. Don't worry, those messages will disappear when you get used to using the correct format."

======

Assuming the dates are in column B. Format that column as date.

Put this macro into that SHEET module:

Please Login or Register  to view this content.

This will change spaces, backslashes and periods into forward slashes which should handle most errors. You can add more...

[JoeyGirl]

Hi JB

Thanks for your response. I didn't think of it like that but you're completely right. To make the fields accept dates in period format was one of the main requests from the projects customer group, but I definately agree the best practice is to condition that department to conform to the correct data entry protocol.

I will go back to their management team and let them know it is possible but it will not necessarily give the best outcome, in the end it's their tool so it's their decision but I'm definately an advocate of your view.

Thank you very much!

[JBeaucaire]

Funny thing, I use Excel 2003 and I can enter dates as 1/1/11 or 1-1-11 or 1.1.11 and they all register as dates with Excel.

[JoeyGirl]

One thing I read in googling before I asked on here is that you can adjust your "Language and Region" settings to make the computer recognize period delimited dates (it was the only 'solution' i could find for every time someone else had asked the question).. so that could be why it works for you?

Unfortunately we've got standard builds with control pannel restrictions that mean we can't even see half the control panel, and there's no way IT are going to go in and adjust the setting on 100 computers so it wasn't quite enough of a solution for us haha