Year calculation in excel

[ramshiva_reddy]

Hello All,

In excel I have a column as “Vehicle Age” and I need to calculate the Year of manufacturing like if Vehicle age is 7 then current year(2023)-7.
In the assign activity I took a variable(Int32) to Cint(Now.Tostring(“yyyy”)
and another assign activity with variable(Int32) to 1stVariable-Cint(currentrow(“Vehicle Age”).tostring). And I added that variable in Add data row activity as int_Vehiclege.tostring
But here I’m getting an error as "Conversions from string “” to type interger is not valid.
Please help me in sorting this issue.

[pravallikapaluri]

Hi @ramshiva_reddy

1. Use the “For Each Row” activity to iterate through each row in the data table.
2. Inside the loop, add an “If” activity with the following condition:

Not String.IsNullOrEmpty(row("Vehicle Age").ToString)

This condition checks if the “Vehicle Age” column value is not empty.
3. Inside the “If” activity, perform the conversion and calculation using the “Assign” activity:

Assign: currentYear = CInt(Now.ToString("yyyy"))
Assign: vehicleAge = CInt(row("Vehicle Age").ToString)
Assign: yearOfManufacturing = currentYear - vehicleAge

4. Add the “yearOfManufacturing” value to the data table using the “Add Data Row” activity.

Hope it helps!!

[ppr (Peter Preuss)]

we can avoid the double round conversion when doing: now.Year

So when in the Age column the year is marked e.g 7 for a 7 years old car we can do
ManfacturerYear = now.AddYears(- CInt(currentrow(“Vehicle Age”).tostring)).Year

When it can happen, that the year is empty or an non valid integer value is present we can check before wit the following condition

( Not isNothing(currentrow(“Vehicle Age”)) AndAlso Int32.TryParse(currentrow(“Vehicle Age”).Trim, nothing)

and get back a true, when we can parse the Age value into an int32