It is because the Date coming from both excel is not in same format.
I can see from the screenshot that some of the date is not in date format. and Thus they are treated as text while loading. And on text you can not use the Month function directly.
Format your excel and select proper formatting for the Date field.
Here are samples of the csv files. I cannot post the actual. But the files go like these: