How can we know latest year and month from existed dates data in excel column?

Hi,
In excel i have date column with values as 22-12-2018 like this format.I need to know latest year & month from that data & get latest 2 months data.

Please guide if anyone knows?

you can use sort datatable activity buddy @SUPRIYA123
–inside the sort datatable activity properties
mention the datatable name, column you want to sort in column name or index, type of sort either ascending or descending and get the output as a sorted datatable
–once that is done you can use for each row loop and iterate through each row in that sorted datatable
–inside the for each row loop use a if condition and mention condition like this buddy
Datetime.Parseexact(row(yourdatecolumnname).ToString,“dd-MM-yyyy”,System.Globalization.CultureInfo.InvariantCulture)>Now.AddMonths(-2)

Kindly try this and let know buddy @SUPRIYA123
Cheers

it is showing error as valid expression ,continuation expected

can i see the error screenshot if possible @SUPRIYA123

error

if i give column name in double quotes,it is throwing error as below screenshot.

error2

After knowing latest year & months from that existed data in excel,i need to filter latest 2 months from that

1 Like

Fine we need to know the format of the datetime been fetched from excel
so kindly do me a favor, get the value from the excel from that column and try to print in output panel with write line activity and check what is the format of datetime is getting printed and kindly share that screenshot as well
this can be resolved easily buddy
Cheers @SUPRIYA123

date

this is the format from excel.

aaha then we need to put like this buddy @SUPRIYA123
Datetime.Parseexact(row(“Inv Date”).ToString,“dd/MM/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture)>Now.AddMonths(-2)

Cheers @SUPRIYA123

run this code.got runtime error like below.

calendar

sorry buddy like this
first comes month and date next but we mentioned date first and month next and there is no month of 21
thats why
it should be like this buddy
sorry for inconvenience
Datetime.Parseexact(row(“Inv Date”).ToString,“MM/dd/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture)>Now.AddMonths(-2)
Cheers

thank u for quick replies.i will try & let u know

Yah sure…
kindly let know if that works or not
This can be sorted buddy
Cheers @SUPRIYA123

System.Exception: Could not retrieve the result of the job execution. This might be because a message was too large to process.

i have excel column range 1 to 205060.is this error regarding that range?

can u tell what is this error mean?

its not due to the file buddy it because when we try to write the data from that file in any console like output panel or message box,remove write line activity if any is included in your workflow

Cheers @SUPRIYA123

ok.thanks.got it.
After if condition how can we write latest months data in excel?

1 Like

yes ofcourse you can write using write cell with value passed as input and the cell range with column and cell number you want
like if you want to enter in E column starting E2
mention like this in range
“E”+Counter.ToString
where Counter is a int32 variable with default value 2, as we are going to start from 2
and after this write cell activity use a assign activity to increment the value of 2 to 3 and 4 and so on,
like this
Counter = Counter + 1

make sure that these two activities are inside the for each row loop and inside the excel application scope
kindly try this and let know buddy
Cheers @SUPRIYA123

then what is datatable input there in write range activity?

by this condition we are taking data in excel i.e output but we should pass datatable input in write cell after that condition.how can we take datatable input?

buddy do you want to write in excel or datatable
if you are directly writing to a excel you can follow previous comments suggestion
or if you want to update to a datatble and then write to a excel
we can just use a assign activity to assign the value to the each row and particular column while being inside a for each row loop, like this in a assign activity
row(“yourcolumnname”) = “the value you want to assign”

Cheers @SUPRIYA123

at this point getting error