Copied Date Data is NOT a Number

Maze123

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 365
Hi,

How do I copy date data retaining its number format?

Rich (BB code):
Dim Sourceworkbook1 As Workbook
Set Sourceworkbook1 = Workbooks.Open("[File path & name]")
Application.ScreenUpdating = False
Sourceworkbook1.Sheets("[tab name]").Copy After:=ThisWorkbook.Sheets("[tabname]")


I have written a matching algorithm which sorts by both an unique identifier and date. If a record falls outside the defined window it is deleted. For a time, the code performed as expected, copying all data from source documents to a Worksheet in another Workbook and then executing. However, one day it just stopped working and began deleting all records. Debugging ensued. Eventually, I found that the date data copied from source documents was not a number (probably a string but unclear) despite originally being of the usual Gregorian kind. I suspect the Date function is not recognising these string dates because they are not numbers.

Please help clarify what is happening and how to fix it.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
It seems like you've worked out what has happened - whereas your algorithm is expecting the data in one format, it is receiving it in another format. As you suggest, probably as a string. Can you show us the code? I imagine the solution will be to work out in what format the data is being submitted to your algorithm, and then work out how to 'clean' it.
 

Maze123

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 365
Hi Dan_W,

Please see attached snippets and code below.
Rich (BB code):
If Worksheets("Rec_9").Cells(r, "B") < DateAdd("h", 17, Date - 3) Then
      Worksheets("Rec_9").Rows(r).EntireRow.Delete
End If

Maybe it would be simpler to xlPasteValuesAndNumberFormats rather than clean after copying. However, my attempts to incorporate PasteSpecial syntax into existing code are messy and I am convinced there is a simple solution in this context. Am I on the right track?
 

Attachments

  • Date.PNG
    Date.PNG
    141.6 KB · Views: 1
  • Date2.PNG
    Date2.PNG
    183.2 KB · Views: 1

Maze123

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 365
Hi Dan_W,

Figured out the xlPasteValuesAndNumberFormat syntax and it lead one step further back, to here:

VBA Code:
Set Sourceworkbook1 = Workbooks.Open("[File path & name]")

Upon comparing the same book opened manually to that of the VBA initiated, I found the date data had been VBA opened as string. I have never encountered this problem before and am genuinely lost. Any insight would be appreciated.
 

Maze123

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 365
Found it! Localised the import to parse data with Local:=True appended at end of parenthesis.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,879
Members
414,413
Latest member
Sinbin

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