Date Formula

Please set the cell format for A2:A3 like this:-

1. Right-click one of the cells and select Format Cells from the context menu
   or select Format Cells from the Ribbon's Home tab.
2. Select the Number tab from the dialog box that opens.
3. On the left, select Custom (at the bottom).
4. In the Type field enter dddd, d mmmm, yyyy
   Enter dd mmmm if you want output of double digit day dates like 01 March.
5. Confirm and exit the Format Cells dialog.

Now, when you enter a true date in the formatted cell (in your sample sheet it might be like =B2 ) it will be displayed as you want it. You can change the cell format and see a different display without changing the cell's value. Beware of "fake" dates. A true date is a number which Excel displays in a format like the one described above. A fake date is one that looks like the display but doesn't have a number behind it.

True dates can be calculated. Like, you can enter =B2+7 to get September 9, or you can calculate the next Monday from the date you entered, or next year. Fake dates have no such capability. They are just text strings which are useless for anything beyond what they appear to be.

Normally, when a cell is formatted as "General" (all cells are formatted as "General" before their format is changed) Excel will look at what you enter and apply a format to suit. When you entered 2-9-2020 Excel recognized a date and recorded a true date. Change the cell format to "Number" and you will see the number Excel generated from your entry - a true date. But when you entered "Monday, 2 September, 2020" Excel didn't see the date and decided that you entered a text string. The result is a fake date. The difference hails from Windows' Regional Settings. There you set "d-m-yyyy" as your default "Short Date" format. So, when you enter a date in such format (Excel can convert 20 to 2020) it will be recognized as a date.

The reverse of the above is a little more complicated, and you made it even more so by asking for d mmmm, yy conversion as well as mmmm d, yy. Here is a formula which must be pasted to row 2 and will convert a fake date into a true one. Of course, the true date can be displayed in whichever way you want. (007)

=DATE(RIGHT(TRIM($A2),4),-INT(FIND(IF(ISERROR(VALUE(RIGHT(LEFT($A2,FIND(CHAR(160),SUBSTITUTE($A2,",",CHAR(160),2))-1),2))),MID($A2,FIND(CHAR(160),SUBSTITUTE($A2," ",CHAR(160),2))+1,3), MID($A2,FIND(" ",$A2)+1,3)),"JanFebMarAprMayJunJulAugSepOctNovDec")/-3),IFERROR(VALUE(RIGHT(LEFT($A2,FIND(CHAR(160),SUBSTITUTE($A2,",",CHAR(160),2))-1),2)),RIGHT(LEFT($A2,FIND(CHAR(160),SUBSTITUTE($A2," ",CHAR(160),2))-1),2)))

The above formula was lifted from cell B2 of the attached workbook's Sheet1. C2 proves that September 2, 2020 is not a Monday. It also shows that the weekday in A2 is ignored. The rest of the worksheet shows the sequence of development of the formula from its humble origin in E6 to the monster it has become above. Knowing how to dissect it will help you modify it which, I hope, you will never need to do :-)

And here is another approach to the same problem.. I sure wished I had thought of it before doing all the work above. Please try the formula below. It does the same job for me. (007)

=VALUE(SUBSTITUTE(FILTERXML("<data><a>"&SUBSTITUTE(A2, ", ","</a><a>",1)&"</a></data>","data/a[2]"),","," "))

What I didn't know is that VALUE("02 September 2020") will retrun a true date. Since it does, I experimented with formulas like this one, which does essentially the same as the the one above. (007)

=IFERROR(VALUE(B13),VALUE(SUBSTITUTE(B13," ",", ",2)))

The results aren't quite the same. Excel seems to require a comma between day and year but object to one between month and year. The XML version doesn't seem to make this distinction but my testing isn't exhaustive. Please look at my results on Sheet2 of the attached workbook.