How to get particular value in excel column

Hi all. i have one excel file. in that excel column name is dateandtime. I need time only in that column could you please help me to get that value.

@Buvaneshwaran_R Have you used Read Range Activity to get the Data as Datatable?
If you have done that then you can just check how the value is appearing for the DateTime column in a Message Box, based on that we can Provide you either a Split method or a DateTime.ParseExact() Method

2 Likes

@supermanPunch Thankyou

Hi @Buvaneshwaran_R

Of what DateTime format the data is present der in your “dateandtime” column ??

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

@Pratik_Wavhal @supermanPunch I tried this method DateTime.ParseExact(row(“Date Entered”).ToString,“MM/dd/yyyy HH:mm:ss tt”,CultureInfo.InvariantCulture).tostring(“HH:mm tt”)
this condition is not working i have attached my excel also can you please check and solve this problem.Book4.xlsx (114.7 KB)

@Buvaneshwaran_R Change the Format to “MM-dd-yyyy HH:mm:ss tt” and check

If the above doesn’t work, change it to “MM-dd-yyyy hh:mm:ss tt” and check

1 Like

Hi @Buvaneshwaran_R

I got the output by using

DateTime.ParseExact(CDate(row("Date Entered")).ToString("MM-dd-yyyy HH:mm:ss tt"),"MM-dd-yyyy HH:mm:ss tt",System.Globalization.CultureInfo.InvariantCulture).ToString("HH:mm:ss tt")

Output :-

  1. By using “HH:mm tt”

  2. By using “HH:mm:ss tt”

Workflow for the same :-
MainPratik.xaml (11.0 KB)

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like

@Pratik_Wavhal Thanksyou

Hi @Buvaneshwaran_R

If the output is the one you were expecting then Mark it as a solution and like it :innocent:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

@Pratik_Wavhal i have another doubt also using these values how to get pst time in excel

Hi @Buvaneshwaran_R

TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Pacific SA Standard Time");

DateTime newDateTime = TimeZoneInfo.ConvertTime(existingDateTime, timeZoneInfo);

Check the above for the same

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like

@DateTime.ParseExact(CDate(row(“Date Entered”)).ToString(“MM-dd-yyyy HH:mm:ss tt”),“MM-dd-yyyy HH:mm:ss tt”,System.Globalization.CultureInfo.InvariantCulture).ToString(“HH:mm:ss tt”) get this date how can i convert to pst time? how can you explain it will be really help ful for me

Hi @Buvaneshwaran_R

Below statement will give the Output in PST

DateTime.ParseExact(CDate(row("Date Entered")).AddMinutes(-750).ToString("MM-dd-yyyy HH:mm:ss tt"),"MM-dd-yyyy HH:mm:ss tt",System.Globalization.CultureInfo.InvariantCulture).ToString("HH:mm:ss tt")

IST time in Excel (i.e of 1st Row) :-
IST = 7-1-2020 12:26:27 am
image

Output in PST (i.e for 1st row value) :-
PST = 11:56:27 AM
image

Converted from Internet the same timing :-
image

Workflow for the same :-
MainPratik.xaml (14.9 KB)
Book4.xlsx (227.1 KB)

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

2 Likes

@Pratik_Wavhal Thank you so much for helpming me.

Hi @Buvaneshwaran_R

If the output is the one you were expecting then Mark it as a solution and like it :innocent:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

@Pratik_Wavhal I don’t have option to mark it as solution.

Hi @Buvaneshwaran_R

Actually Till now i haven’t created any post :sweat_smile:

So i dont know where the option is der to mark it as solution.

But i told u to mark so if anyone searching the solution for same or similar query then it helps to find them the exact solution. So i told you to mark it as solution.

May be the same option to mark it as a solution must be der for u that u have already used in the previous posts you have created and those got solved/ got solution.

Like this u did previously as shown in below thread :-

Let it be. If you find the option then definetly mark it.

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer: