Formatting cell problem

alex_peng

New Member
Joined
Jun 29, 2020
Messages
35
Office Version
  1. 2013
)​
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

New Member
Joined
Jun 29, 2020
Messages
35
Office Version
  1. 2013
B2 shows what appears to be a date but you can see what it shows in the value box is different.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
As I have asked what does the code I posted return.
 

alex_peng

New Member
Joined
Jun 29, 2020
Messages
35
Office Version
  1. 2013
IMG_7912.jpg
IMG_7912.jpg
IMG_7911.jpg
 

alex_peng

New Member
Joined
Jun 29, 2020
Messages
35
Office Version
  1. 2013

ADVERTISEMENT

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

alex_peng

New Member
Joined
Jun 29, 2020
Messages
35
Office Version
  1. 2013
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

New Member
Joined
Jun 29, 2020
Messages
35
Office Version
  1. 2013
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top