Excel formats rows differently and changes format

[Brutusar]

Hi, I have a strange problem,

After scraping some data from a website and stored it into a data table, it is written into Excel. This is usually 10-15 rows. In the data table, one column is dates in the format dd/mm/yyyy. When this data is written into Excel, some cells are being formatted as "General", and some as "Date". (This is happening "by itself" when the data are written in). Then in some cells, formatted as "Date", day and month switches places. For example, 11/04/2020 suddenly becomes 04/11/2020. This seems to happen completely by random.

Does anyone have an idea why this is happening?

 

[mse330]

Hi Brutusar,

I have encountered a similar issue before when I exported one report from a system into excel. The system was generating the report in US date format mm/dd/yy whereas the date format we use in Kuwait is dd/mm/yy. I believe you could solve it by either changing the date format in your windows regional setting or I used a formula to convert my dates to the correct format like below

Book1
	A	B
1	System Date	Formula
2	05/31/20	31-May-20
3	05-12-20	12-May-20
4	05/20/20	20-May-20
5	05/13/20	13-May-20
6	05-07-20	07-May-20
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(RIGHT(A2,2)+2000,LEFT(A2,2),MID(A2,4,2)))

 

[Brutusar]

Thanks, I will try that!