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
So apparently i have dyslexia......ws3 was HOEP1 and not HOEPI I appreciate your help.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Happy that you got it sorted.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,344
Members
416,096
Latest member
forevans

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