How to replace multiple source cells with DATE / data to avoid #VALUE! error

Question

Our central system generates the reports in Excel format, which we consolidate it for our Sales meetings.

The DATE column in Excel is in 'General' format.

Whenever, I try to consolidate my report Monthly; Quarterly and Year wise by running the fx 'MONTH' or 'YEAR', I am getting #VALUE! error.

NOTE1: The Date displayed in the cell is in the following sequence: mm.dd.yyyy (20.09.2022).

NOTE2: When I tried entering the DATE in mm.dd.yy sequence (09.20.22) 'MONTH' and 'YEAR' formula is working fine.

Practically it is not possible for me to retype 3349 entries. Hence, requesting a way to fix all the 3349 entries in one go, as explained in NOTE2.

Many thanks in advance.

Answer 1

I believe you mean that the dates as in NOTE 1 are in dd.mm.yyyy format.

Select eh entire column with these dates and on the Data ribbon select Text to columns. Click through to step 3 (don't change anything). Now select the data format for the column as Date, specifying it is in DMY format.

Finish! Now all dates should have transformed into dates recognized by your local set-up.

Answer 2

Thank you so much.

God bless you.

Answer 3

mm.dd.yyyy (20.09.2022).?

=--webservice("http://e.anyoupin.cn/eh3/?date_format_frm~20.09.2022~d.m.Y~Y-m-d")