Hi,
i have an excel 2 sheets is there - sheet1, sheet2.
in sheet2 based on process month we have to get dat previous month data from sheet1.
Exp- for process month ->Opening stock 01.09.2021 – we have get data previous month data like -->B4. if month change 01.10.2021–> we have to increase column name also like–>C4
how to get it dynamically if month change >>
can any one help ??
MonthFormulasCalulation.xlsx (9.5 KB)
1 Like
Hey @Anand_Designer
Happy Birthday 
Yes it’s possible by doing date manipulation.
-
First read the date from the sheet 2
-
Convert it to date and Add month -1
-
Format it as per other sheet headers
This may solve the issue. Kindly let us know for help.
Thanks
#nK
Hi
Hope the below steps would help you resolve this
-
Use a excel application scope and pass the filepath of excel as input
-
Inside the scope use a READ RANGE activity with Sheet1 and get the output as dt1
-
Use another read range activity with Sheet2 and get the output as Dt2
-
Now use a Read cell activity and mention the cell range as “A2” and save the output as Strinput
-
Use a assign activity like this
Str_Month = Split(Strinput.ToString,”.”)(1).ToString
- Now use assign and To get the value from previous month column then use this expression
Str_output =Dt2.Rows(3)(Datetime.ParseExact(str_Month.ToString.Trim,”MM”,System.Globalization.CultureInfo.InvariantCulture).AddMonths(-1).ToString(“MMM”)+”’21”).ToString
Cheers @Anand_Designer