Formatting cell problem

[alex_peng]

)​

I am having an issue converting a cell in an imported xml table. The date cell in the xml table comes in as a "custom" format and is structured like a standard date format(12-Jun-20) when i click on cell if I look in the box that displays the value it shows me the date I am actually looking for (2020-06-12).
In excel if i select cell properties and change format to date it changes it correctly to the date format I am looking for (2020-06-12)
The problem is in vba. I need to extract the month "mm" and use in a later formula. Here is a snippet of my code.

IESODATMonthRaw = ws3.Range("b2").NumberFormat = "date"
Or
IESODATMonthRaw = ws3.Range("b2").NumberFormat = "yyy-mm-dd"
Both these statements return the wrong data. Gives me the following
12:00:00:AM
I have another statement later that extracts the month from it and it returns a value of 12. (which it looks like it is getting from the original data)
IESODATMonth = Month(IESODATMonthRaw)

 

[alex_peng]

B2 shows what appears to be a date but you can see what it shows in the value box is different.

 

[MARK858]

As I have asked what does the code I posted return.

 

[alex_peng]

 

[MARK858]

I give up.

 

[alex_peng]

PI00030844
12-Jun-20​
108091​
127071728RT002
``

 

[alex_peng]

Im not sure you can see the excel cell shows 12-jun-20 is there a way to post the excel file. Does it have anything to do with it being a custom data type in the b2 cell??

 

[MARK858]

Not sure what your last image is supposed to tell me but the only way that you can get a 1 from b2 if b2 in the sheet that you want has 43994 in the cell is if ws3 is referring to the wrong sheet.

Edit

Does it have anything to do with it being a custom data type in the b2 cell??

No, formats do not affect the underlying value.

 

[alex_peng]

I dont get it ... i check the ws3 and it is definitely pointing to the correct sheet. I copied the section of the worksheet above so you can see the value in B2 is 12-12-20. The only thing I can do is post my excel sheet and see if you see anything else. This is definitely frustrating

 

[alex_peng]

sorry 12-jun-20

 

[MARK858]

Upload your file to a free file hosting site (www.box.com or www.dropbox.com), mark the file for sharing and post the link it provides in the thread.