Problem by entering date in excel

I don't think you can do this in the same cell (maybe with VBA - but that's not for me), but you can - in an adjoining cell. Does this do what you want (assumes "date" is in A1)? There will be nicer (shorter) ways to achieve this, though. We'll see !!

Formula:

Please Login or Register  to view this content.

If you re-read the first sentence of my post - I did understand. I don't think that it can be done without VBA.

Shorter formula, assuming that the day is always two digits:

Formula:

Please Login or Register  to view this content.

However, as Glenn says, you can't correct the value in the cell without VBA

You could, maybe use Data Validation to force a valid date to be input in the first place which would be more sensible, IMO.


Regards, TMS

You're welcome. Thanks for the rep.


What solution did you go for? The Data Validation route?


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.