How do I ensure the cell value of downloaded item that occasionally is formatted as Custom date (1 cell), is formatted as General

gsdanger

Board Regular
Joined
Jul 12, 2010
Messages
75
Office Version
  1. 2019
Platform
  1. Windows
Good Day all,
I download into excel the value of a cell from the web. Normally the cell data is formatted as General. However, occasionally the originators of this data format this cell as Custom - as a date).
This is very frustrating, as I want the imported data to be formatted as General. I have tried to re-format the cell however it doesn't respond to the new formatted request.
Examples:
The cell value before I download it is: 3: 0-1-2 ( I have the destination cell formatted as General).
What I get is 1/02/2022 3:00 (it is formatted as Custom: d/mm/yyyy h:mm)
When I attempt to re-format this cell to General, it get: 44593.125

I was thinking of using IFERROR to try and fix this problem. However, not sure if this will work, as it isn't actually an error, just the wrong format.
I was also thinking of setting a default value into my spreadsheet when this problem arises (would like it to default to: 0: 0-0-0).

Can anyone assist me with this problem as it is very frustrating.....it's doing my head in!!

Kind Regards.
gsdanger
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,991
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The only reliable way for importing info from the web is considering them as "text"
Thus when you set the information in the cell, add the text marker; I use this format:
Instead of (for example) Range("A2").Value = myItem.innerText
VBA Code:
Range("A2").Value = chr(39) & myItem.innerText
Of course you can perform other actions, if you know the type of information you are dealing with; for example, if it is a stock price then you will convert the info into a number
VBA Code:
Range("A2").Value = CSng(myItem.innerText)

Bye
 

Forum statistics

Threads
1,175,962
Messages
5,900,557
Members
434,836
Latest member
YetAnotherDave

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