Linq query to Convert Date to Date Serial Number i.e. the general format of Date in Excel

Help Studio
1

Hi All,
I’m trying to get A date 08/14/2021 converted to 45152 which is the general format of the date in Excel to a new column . The logic behind this subtracting 08/14/2021 with 01/01/1900. Can you please help me with a linq query for performing this one

2

Hi @Amrutha.mg
Try this:

Dim newDt As DataTable = dt.Clone()
newDt.Columns.Add("SerialNumberColumn", GetType(Double))

dt.AsEnumerable().ToList().ForEach(Sub(row) newDt.Rows.Add(row.ItemArray.Concat({(CDate(row("DateColumn")) - New DateTime(1900, 1, 1)).TotalDays}).ToArray()))

This code will create a new DataTable newDt with the same structure as your original DataTable dt, along with an additional “SerialNumberColumn” that holds the Excel date serial numbers.

Remember to adjust the column names according to your actual data structure.

3

Hi,

Thank you Its working

1 Like
4

@Amrutha.mg

DT_Input.Columns(“Total”).Expression =

“Convert(columnname, System.Datetime’) -Convert(columnname,System.Datetime’)”

Create a column before it by using add data column with name as Total

Use it in assign activity

Cheers

5

You’re welvome @Amrutha.mg

Happy Automation

Regards

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.