VBA to convert column range of date to long int

[Mr80s]

Hi Folks,

I have a script that copies ranges (columns) of data from one workbook to another. It copies a column of Dates in format MM/DD/YYYY and I'd like to convert that entire column to long integer.

So here is my code that copies the columns over:

Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range, fileLocation As StringDim fileName As String
Set sh1 = ThisWorkbook.ActiveSheet
nmary = Array("Last Name", "First Name", "Student ID", "Role", "End Date", "School Email Address")
Workbooks.Add
Dim col As Range
Dim value(0) As Integer
Set sh2 = ActiveWorkbook.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(inputValue).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
    Next

I then do some renaming of my column headers:

sh2.Columns.AutoFit
sh2.Range("A:A").Replace "*Last Name*", "sN", xlWhole
sh2.Range("B:B").Replace "*First Name*", "givenName", xlPart
sh2.Range("C:C").Replace "*Student ID*", "otherIpPhone", xlPart
sh2.Range("D:D").Replace "Role", "templateName", xlPart
sh2.Range("E:E").Replace "*End Date*", "accountExpires", xlPart
sh2.Range("F:F").Replace "*School Email Address*", "mail", xlPart

What I'd like to do is now take all of Column E "End Date" of format MM/DD/YYYY and convert to long integer (needs to be that format to be imported into another program).

I'm trying :

Dim datevar As Long
For i = 1 To Rows.Count
 datevar = CLng(Cells(i, 5))
 Cells(i, 5) = datevar
Next i

but this A) is way to inefficient and B) doesn't end up converting.

Any ideas?

Thanks!

 

[Joe4]

Is your date entered as a valid date or text?
What exactly are you converting it to?
I ask because Excel already stores dates as integers, specifically the number of days since 1/0/1900. So all you would need to do is change the format to numeric.

However, if what you really want to do is convert a date like 10/15/2019 to 10152019, that is a bit different. You could do something like:

datevar = Format(Cells(i,5),"mmddyyyy")+0

Also, what you intend to do about leading zeroes, i.e. should 01/15/2019 becaome 1152019 or 01152019?
If the second, then it is not really a number, as leading zeroes have no meaning to numbers (like integers).

 

[Mr80s]

Hi Joe4,

Thanks for the quick reply. I'm looking to convert dates to long integers not from 10/15/2019 to 10152019. But instead 10/15/2019 to 132155712000000000

So basically what you're saying is something like:

sh2.Columns("E"). _
NumberFormat

Question, (and yes I'm still new to VBA) to start column E at E2 (to exclude the header) what is the syntax? Also Would it be NumberFormat = "0" (I don't see an option for LongInt).

Thanks!

 

[Joe4]

But instead 10/15/2019 to 132155712000000000

Please explain exactly how you get from 10/15/2019 to 132155712000000000.
What is the logic?

 

[Mr80s]

Well, I'm using Excel to create a .CSV file which is used by another program. I can "manually" convert each cell using this formula. In this example 10/15/2019 is in A1



=IF(A1>0,(A1+109205)*8.64*10^11,"0")

Basically, I'm looking to convert 10/15/2019 to the Long Int so I can set accountExpires AD attribute which uses longint. I was thinking CLng might work....

Thanks again!

 

[Joe4]

=IF(A1>0,(A1+109205)*8.64*10^11,"0")

Wow, I would love to know the logic behind that formula, what is actually really supposed to represent! I have never seen that type of conversion done on a date before.

In any event, you could get it to display the value you want like this:

=TEXT(IF(A1>0,(A1+109205)*8.64*10^11,"0"),"0000000000000000")

So one way, would be to insert a blank column to the right of your current date column, enter the formula and copy down for all rows, convert all the formulas in the column to values, and delete your original date column.
You can get almost all the VBA code you need to do this by turning on your Macro Recorder, and record yourself doing these steps manually.

 

[Mr80s]

Hi Joe4,

Thanks for taking the time to help.

I found that formula off the net. I'm not sure it's actually what I needed.

Basically, I'm trying to convert dates in Excel from 10/15/2019 to the 18 digit Active Directory timestamp or sometimes called Windows NT Time. It's used for setting the account expiry dates in Active Directory.
What I'm really looking for is the formula or math or logic to convert 10/15/2019. Searching provides a lot of examples of how to convert from AD Time Stamp to date but not the other way around.

I also read from the company who's tool I use to manage AD that accountExpires attribute is in Long Int format. So my original question was how to convert 10/15/2019 to long integer.

Anyway, thanks for trying and I'll keep searching on how to convert, then I'll use the Macro Recorder to create it.

Cheers

 

[Joe4]

So my original question was how to convert 10/15/2019 to long integer.

That statement, in and of itself, is incomplete, and does not give enough information. A simple "data type" conversion will not do what you want.
You can easily convert a date to an Integer on an Excel sheet, simply by changing the Format of the cell to "General" or "Number", since Excel really stores dates as a number in the first place (they are just numbers with a special date format applied). However, while that may change it to a number, it is probably not the "number" you are looking for.

Most type of conversion involve a conversion formula or method, so that formula or method needs to be identified and supplied in order to program for it. For example, in order to convert from temperature in Fahrenheit to Celsius, you have to use a specific conversion formula. Or if you want to convert feet to meters, you would use a different conversion formula.

So to get from a "standard" date to the date in Active Directory timestamp formula, you need to use some sort of conversion formula. If the formula you provided in the previous post is accurate, then you should be able to take that, and the recommendation I gave you to give you exactly what you need.

If it does not, or you are having trouble implementing the steps I gave you, please let me know.

 

[Joe4]

From what I found here: https://social.technet.microsoft.co...-directory-lastlogontimestamp-conversion.aspx, it appears that the formula you posted should work.

So, if you follow the steps I gave you, here is what the code you would record should look like (I added some documentation and did a little clean-up):

'   Find last row with data in column E
    Dim lr As Long
    lr = Cells(Rows.Count, "E").End(xlUp).Row
'   Insert blank row
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").ColumnWidth = 30
'   Populate conversion formula in column F
    Range("F1:F" & lr).FormulaR1C1 = _
        "=TEXT(IF(RC[-1]>0,(RC[-1]+109205)*8.64*10^11,""0""),""0000000000000000"")"
'   Convert formulas in column F to values
    Range("F1:F" & lr).Copy
    Range("F1:F" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
'   Delete column E
    Columns("E:E").Delete Shift:=xlToLeft

So that block of code above should do what you want, with no loops.

 

[Mr80s]

Hey Joe4,

Thank you so much for taking the time to work through this and clearly explaining (and commenting in code).

I got it to work but with a couple tweaks needed:
1) I changed the formula as the above seemed to be off by 2 days.
2) I had to set the numberFormat of the column first before applying the formula.
3) Did a little massaging of the headings and cut & pastes.

It may not be the cleanest code, but it works and I appreciate your help!!

Here is what I got to work...

'   Find last row with data in column E
    Dim lr As Long
    lr = sh2.Cells(Rows.Count, "E").End(xlUp).Row
'   Insert blank row
    sh2.Range("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    sh2.Columns("F:F").ColumnWidth = 30
    sh2.Columns("F:F").NumberFormat = "0"
'   Populate conversion formula in column F
    sh2.Range("F2:F" & lr).Formula = _
        "=TEXT(((($E2-25569)*86400000+11644473600000)*10000),""0000000000000000"")"
'   Convert formulas in column F to values
    sh2.Range("F2:F" & lr).Copy
    sh2.Range("F2:F" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
'   Delete column E
    sh2.Range("E2:E" & lr).Delete Shift:=xlToLeft
    sh2.Columns("E:E").ColumnWidth = 30
    sh2.Range("G1").Cut Range("F1")
    sh2.Range("F1").value = "mail"

Thanks again!!