Converting the date time values in a datatable into just date

Help Studio
, ,
1

Hi,
I have attached an excel sheet containing the data where in the date columns ,if there are any time present along with the date we just need to convert it to date.
Test2.xlsx (10.3 KB)
Currently in the sheet there are no time values present in the sheet.
But we need to handle if there are so I am trying to use a linq query for the same but it is giving errors.
The query we are using is:-
(From x In resulting_DT
Let ra = resulting_DT.Columns.cast(Of DataColumn).Select(Function(c)If(columnNames.Contains(c.ColumnName) AndAlso Regex.IsMatch(x(c.ColumnName).ToString,String.Join(“|”,dateFormats.Select(Function(d)Regex.Replace(d,“[^:\/-]”,“\d”)).toarray)),DateTime.ParseExact(x(c.ColumnName).ToString,dateFormats,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString(RequiredDateFormat),x(c.ColumnName).ToString)).ToArray
Select resulting_DT.Rows.Add(ra)).CopyToDatatable
Let me know if we can get any solution for the same.
Thanks in advance!!

2

@Kunal_Jain

one way is use format cells and set required date format only and read the data with preserve format or display values option

cheers

cheers

3

Hi @Anil_G
Format cell is not working actually. It is not giving the desired result, not removing time from the column.

4

@Kunal_Jain

If you need the time to be ignored in datatable then you need to use display value or preserve format or else you would get it by default

Cheers

5

@Anil_G @Palaniyappan @supermanPunch @Yoichi
Can anyone suggest a way how to avoid time while writing the data into “Excel” without using preserve format method.
Actually While writing the data it is converting into date time. only need date format as mentioned above.
Thanks in advance!!

6

@Kunal_Jain

By default excel stored the data in date time format only…

Preserve format is a way or after reading convert the data as needed…no other way for you

Cheers

7

@Anil_G
How to convert the date.
I have provided the method I used to follow but I am getting issues in windows with the same code.
Thanks!!

8

Hi @Kunal_Jain ,

Could you provide us with the Screenshots of the Implementation done till now ?

We do have some confusions on the implementation as few things are working from our end when we checked. Used Workbook Read Range and Write Range activities, there were no time format in the resulting Excel sheet.

Do you need the format to be removed in the Datatable alone for further computations ?

9

There is no such thing as “just a date” when it comes to datetime values. They always contain a time portion. What’s really happening is your Excel sheet is formated to display them as date and time. You can just edit your spreadsheet and format that column to display date only.

10

Hi @supermanPunch, actually the data I am reading has no time with date but while pasting the data in the new sheet it is giving me date with time.
image

image
image1371×188 42.1 KB

I have attached screenshots for the same.
Thanks!!