How to replace date and remove middle name from excel

Help Studio
, ,
1

Hi Team,
I have three columns - Consultant,DocumentDate,ServiceDate
I have three scenarios,

  1. If the ServiceDate consists of the year ‘1900’ then use the document date.
  2. If Consultant has 3 (First middle Last) names, then remove the Middle name. and if it has more than 3 then ignore.
  3. Concatenate both Consultant and ServiceDate.
    I have tried using keystrokes, but it is taking a more time and updating each row.
    Is there any possibility to do faster without keystrokes.

Attached the sample file.

Logic
Logic1272×271 12.1 KB
Sample File.xlsx (72.4 KB)

2

Any inputs on this please? @ppr

3

The simplest and fastest way would be - Read the excel and create datatables for all the 3 actions that you mentioned, Perform the 3 actions programatically to the datatables and write them back to excel.

4

Thank you for the idea, I have tried creating the dt tables and got stuck in performing this operation.

If the ServiceDate consists of the year ‘1900’ then use the document date.

How to achieve this programatically?

5

I have tried the second scenario. but couldn’t able to get the result,
could you please assist.
Sample File.xlsx (72.4 KB)
MiddleName_Bot.xaml (10.3 KB)

6

Loop through datatable and for each row, Check if the column that has service date has year 1900, if there is a match then assign the column value of ‘Document date’ to column ‘Required service end date’.

7

Hi @Vishwanth_Raya ,

Please change the file path and delete column B in Final sheet and test it.

Sample File.xlsx (77.9 KB) ExcelDtLogic.xaml (12.1 KB)

Thanks!

1 Like
8

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