Allow date format entry as both US and UK on one sheet.

Hello to the forum from a 'low altitude' user.
Creating a workbook on 2016Excel UK for use abroad by a friend on Mac 2011Excel in US format.
Just successfully tested a workbook for data entry with UserForm, comboboxes and date range validation created in the above scenario.

The only real issue (at the moment) is the entering of dates in US format that are not accepted by my sheet created in UK format.
Having done the usual inquisition of Mrs Google and family I can only find answers to force one or other format but nothing to allow both.

I am aware that I could just make the sheet only US date format but was hoping for a multi format attempt first.
"US" only will be my method if all else fails.

Currently using basic validation - Allow Date - Between - Start/End and with Ignore blanks checked.
And I have no way to test variations at this end ...

Could ? the ignore blanks be helpful in allowing any data entry if I could use a named range (in use already for the combos) and added a blank option ?
Or is there a better / more efficient / reliable method ?

All this needs only to work on a single sheet in one workbook.

Having dates in mixed formats on one sheet
No doubt one of our resident 'Guru's' will take up the challenge.
As an aside you could use a date picker (as attached file).
If you alter the format to mm/dd/yyyy in the BtnClass module you will get US format transferring to the UserForm and sheet.
torachan.

Thank you "torachan".
I will look at that method ... but would you say that this is an advanced way of me just converting the sheet that gets returned to me ... ie I just send out as US format and convert back to UK format on receipt ?
As an aside ... this sheet is to help me with data entry into Access tables after transcription from original historical paper documents.
Lots to do but the conversion from US to UK will be irregular.
Paul

Ignore my post, got the wrong end of the tale, I thought you and your American colleague were using the same app, but you wanted his to accept his format.
Dates are the biggest p.i.a. not only have to contend with Excel formatting but also the locale settings of the computer.
Are you manually inputting an American paper document into Excel now and you want to advance to using an American produced Excel file to save time.
If this is the case and the dates are known to be American format, then run a macro on the file to convert them.
torachan.

Thank you "torachan".

The documents have varying date formats as they are handwritten ... but my friend is more used to US dates when typing and machine is set to US locale.
I think the best way is to set the relavent columns to US date format for data entry and then do as you suggest with a macro.
Consensus seems to be that it is overkill to try both formats at same time, especially when teh conversio is non critical and occasional.
So solved from my point of view.
Thanks again
Paul

Thanks for the feedback.
I have used the macro below in the past when I know the date is definitely US format.
It converts a highlighted selection to text -feeds it back to the column and reformats to "dd/mm/yyyy"

Please Login or Register  to view this content.