In column D I have data in the format year / month / day. My goal is to get information in column H about the total number of days in relation to column D.
First I transferred the data from column D to columns E: G and tried to collect it in the end. It seems that the data from the E: G column is in the form of a text and not a number, and therefore it cannot perform the calculation for me.
Idea how to calculate this?
Sub Macro1()
'
' first attempt
'
Dim book
Dim sht As Worksheet
Dim rng As Range
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Range("E1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Day"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Sum"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]*365)+(RC[-2]*12)+RC[-1]"
Set rng = Range("H2:H" & Cells(Rows.Count, "D").End(xlUp).Row)
With rng
.Formula = "=(RC[-3]*365)+(RC[-2]*12)+RC[-1]"
.Value = .Value
End With
End Sub
Bookmarks