Forcing input format for dates?

[EirikDaude]

I'm having a spreadsheet in which the user is entering the time at which a task is finished. Because this time is later used to calculate the difference between when the task was planned to be done I also need the date, but since I don't want the sheet to look to messy, I'd prefer not to have to display it. I'd also like to avoid using an input-box/form to enter the information.

Now, the problem is that the input-format for dates varies from computer to computer (and maybe from user to user as well) where the worksheet can be accessed. What I'd like is for the user to put in e.g. "6/2 11:00", and for that to then be identified as the 6th of February of the current year by the worksheet. So is there any way to force this? Currently there's a fair chance for it to be interpreted as the 2nd of June, depending a bit on where/who puts it in.

Attaching a sample sheet from the workbook, for those in need of a more visual aid

[Bob Phillips]

That should never be interpreted as 2nd June unless the computer has US settings. With European settings it should always be seen as 6th Feb.

[EirikDaude]

Sadly someone have US settings, then. I take it it's not trivial to force the worksheet to always interpret it as 6th February?

[Bob Phillips]

It shouldn't matter. Excel stores a date as an integer since 1st Jan 1900, so whatever date settings they have, entering a date of 2nd Feb will be stored as 41311 irrespective. So if someone in the US enters a date of 2/6 it will still be held as 41311.

[EirikDaude]

Yes, but my problem is that people somewhere are entering 6/2 meaning for it to be stored as 41311 while it actually is stored as 41427. I could of course ask the users to check this before continuing with their work, or try to find out which users/computers it is that's set up with US settings, but it would be a bit simpler for me to just validate the input using Excel and/or VBA.

[snb]

You can use validation.
give cellD5 a kind of validation.

after that use this macro:
[VBA]
Private Sub Worksheet_Activate()
Cells(5, 4).Validation.Modify xlValidateList, , , Join([transpose(text(A5+(row(1:24)-1)/24,"dd-mm-yyyy hh:mm "))], ",")
End Sub

[/VBA]

[Bob Phillips]

Why would anyone in the US enter 6/2 when they wanted 6th Feb. That is the only way I can see the value of 41427 being entered. Excel should manage it all for you, just as it does with formulas. Even if the cell is formatted as m/d because it originates in the US, if I enter 6/2 it is 6th Feb.

Can you post a workbook that shows the problem?