Date formatting

[debbys]

Hi There

Hopefully a really quick one. We have a doc that has the date entered with . as oppose to / - how can I set the cell formatting so that if someone enters 1.1.21 that it converts to 01.01.21 in the same way that it does when using /?

I thought it would simply be formatting the cells to dd.mm.yy but that isn't working

Many thanks

[Pete_UK]

First you need to convert those dates which have full-stops into proper dates, as the full-stop will not be recognised by Excel and thus the "dates" will be text values. To do this, just select the column, then CTRL-H to pull up the Find & Replace dialogue box and change . to / for all.

Then you can apply a Custom format to those dates of : dd.mm.yyyy

Note, though, that if a user inputs a new date, then s/he must use the solidus ( / ) to separate the date elements, and not a full-stop.

Hope this helps.

Pete

[debbys]

Thanks Pete, that makes sense