Data Get Dynamically based on process month using Formulas

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)

2 Likes

Hey @Anand_Designer

Happy Birthday :confetti_ball:

Yes it’s possible by doing date manipulation.

  1. First read the date from the sheet 2

  2. Convert it to date and Add month -1

  3. 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

  1. Use a excel application scope and pass the filepath of excel as input

  2. Inside the scope use a READ RANGE activity with Sheet1 and get the output as dt1

  3. Use another read range activity with Sheet2 and get the output as Dt2

  4. Now use a Read cell activity and mention the cell range as “A2” and save the output as Strinput

  5. Use a assign activity like this

Str_Month = Split(Strinput.ToString,”.”)(1).ToString

  1. 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