Time Format Convert in Linq

[Shyam_Pragash]

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

Thanks
Shyam

[Yoichi]

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,

[Shyam_Pragash]

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

[Yoichi]

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
)

[Shyam_Pragash]

Hi @Yoichi

Yes… Thanks

[Shyam_Pragash]

Hi @Yoichi

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

Thanks
Shyam

[Yoichi]

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,

[Shyam_Pragash]

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

[Yoichi]

Hi,

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

Regards,

[Shyam_Pragash]

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

[Yoichi]

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,

[Shyam_Pragash]

Hi @Yoichi

error

what is this error ?

Thank
Shyam

[Yoichi]

Hi,

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

Regards,

[Shyam_Pragash]

Hi @Yoichi

invoke code activity has error.

[Yoichi]

Hi,

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

Regards,

[Shyam_Pragash]

Hi @Yoichi

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

if cell is black write “00:00”

[Yoichi]

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
)

[Shyam_Pragash]

Hi @Yoichi

Thanks you…

Its Perfect…

Thanks
Shyam