Time Format Convert in Linq

Hi…

How to convert Time difference types of times present in the excel sheet…
so many colums present in the sheet. i have to change this columns only
Input:

Sl.No. Date Time VRS RTx Cvz
1 06-05-2022 5 xx xx xx
2 06-05-2022 06:20 xx xx xx
3 06-05-2022 6.48 xx xx xx
4 06-05-2022 6.45 AM xx xx xx
5 06-05-2022 0 xx xx xx
6 06-05-2022 xx xx xx

Expected Output
image

Thanks
Shyam

Hi,

How about the following?

dt.AsEnumerable.ToList.ForEach(Sub(r)
r("Time")=DateTime.ParseExact(r("Date").ToString+" "+r("Time").ToString,formats,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
End Sub
)

Sample20220506-3.zip (9.1 KB)

Regards,

Hi @Yoichi

you are declared different types Formats in the Assign Actvity…

is it possible to without declare the format to convert it using LINQ?

Thanks
Shyam

Hi,

We can write it in LINQ expression directly as the following. Is this what you expect?

dt.AsEnumerable.ToList.ForEach(Sub(r)
r("Time")=DateTime.ParseExact(r("Date").ToString+" "+r("Time").ToString,{"dd-MM-yyyy H","dd-MM-yyyy H:m","dd-MM-yyyy H.m","dd-MM-yyyy h.m tt","dd-MM-yyyy "},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
End Sub
)

Hi @Yoichi

Yes… Thanks :slight_smile:

1 Like

Hi @Yoichi

i want to convert time columns only but your are mentioned date columns also. Why?

Thanks
Shyam

Hi,

Because there is AM/PM in some cells. As we need to parse AM/PM using DataTime, the above expression uses Date column. And it’s also simple if blank cell exists.

Regards,

Hi @Yoichi

Ok… i am trying to do Without AM/PM on your above code…

and

is single digit of Number “5”

i got error

Thanks
Shyam

Hi,

In my sample, “5” works well, does it?
Did you modify expression?

Regards,

Hi @Yoichi

Your code is working fine…

i am try do different types time format in single file… like this input…
1.

Sl.No. Time VRS RTx Cvz
1 5 xx xx xx
2 7 xx xx xx
3 12 xx xx xx
Sl.No. Time VRS RTx Cvz
1 6.3 xx xx xx
2 5.35 xx xx xx
and so on…

Without use Date… Only convert time only…

Thank
Shyam

Hi,

Can you try the following?

dt.AsEnumerable.ToList.ForEach(Sub(r)
r("Time")=DateTime.ParseExact(r("Time").ToString,{"%H","H:m","H.m","h.m tt"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
End Sub
)

This can handle even if AM/PM exists. However throw exception if blank cell exists.

Regards,

Hi @Yoichi

error

image

what is this error ?

Thank
Shyam

Hi,

Do you use InvokeCode activity? The expression doesn’t work in Assign activity.

Regards,

Hi @Yoichi

invoke code activity has error.

Hi,

The expression throw exception if blank cell exists. can you check it in source xlsx file?

Regards,

Hi @Yoichi

data file… :
data.xlsx (9.0 KB)
One cell is blank

if cell is black write “00:00

Hi,

C7 of the above xlsx is blank. If we need to handle blank cell, the following will work.

dt.AsEnumerable.ToList.ForEach(Sub(r)
    If DateTime.TryParseExact(r("Time").ToString,{"%H","H:m","H.m","h.m tt"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None,New DateTime) Then    
        r("Time")=DateTime.ParseExact(r("Time").ToString,{"%H","H:m","H.m","h.m tt"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
    Else
        r("Time")="00:00"
    End If
End Sub
)

Hi @Yoichi

Thanks you… :slight_smile:

Its Perfect…

Thanks
Shyam

1 Like

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