How to get the Interval of the month based on the excel file

Hello all,
I need to get all the interval of the month based on the excel file.

Output should be in the Ro (Romanian) format

Here is sample input and output excel

Sample Data.xlsx (9.0 KB)

@ashwin.ashok @supermanPunch @Nithinkrishna @ppr @Yoichi @lakshman

Thanks in advance

1 Like

Hello @Marian_B ,

The year will be always 2021 or 2022? Is there any chance that other years can also come?

Thanks!
Athira

Hello @athira.somasekharan

Thanks for the quick respond

Yes may be in future it will come

Hi @Marian_B ,

Is this the expected output?
image

If so, then here is a snippet of code which you can use to achieve the operation →

(From row In dt.AsEnumerable()
Let firsthalf = Convert.ToDateTime(String.Format("{0} {1} {2}","01",row("MONTH START").ToString,(CInt(row("YEAR END").ToString)-1).ToString),New System.Globalization.CultureInfo("ro-RO"))
Let secondHalf = Convert.ToDateTime(String.Format("{0} {1} {2}","01",row("MONTH END").ToString,row("YEAR END").ToString),New System.Globalization.CultureInfo("ro-RO"))
Let range = Convert.ToInt32(Math.Abs(DateDiff(DateInterval.Month, secondHalf,firstHalf)))
Let months = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(String.Join(",",Enumerable.Range(0,range+1).Select(Function(s) (firsthalf.AddMonths(s).ToString("MMMM",New System.Globalization.CultureInfo("ro-RO"))))))
Let ra = New Object(){row("MONTH START"),row("YEAR START"),row("MONTH END"),row("YEAR END"),"",months}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

DateIntervalInRomanian.xaml (6.5 KB)

Kind Regards,
Ashwin A.K

1 Like

Hi,

Another solution:

If you need string array as a result, the following will work.

arrResult = dt.AsEnumerable.Select(Function(r) String.Join(",",Enumerable.Range(0,1+CInt(DateDiff(DateInterval.Month, DateTime.Parse(r("MONTH START").ToString+" 1,"+r("YEAR START").ToString,New System.Globalization.CultureInfo("ro-RO")),DateTime.Parse(r("MONTH END").ToString+" 1,"+r("YEAR END").ToString,New System.Globalization.CultureInfo("ro-RO"))))).Select(Function(i) DateTime.Parse(r("MONTH START").ToString+" 1,"+r("YEAR START").ToString,New System.Globalization.CultureInfo("ro-RO")).AddMonths(i).ToString("MMMM")))).ToArray

Sample20220406-6.zip (8.9 KB)

Regards,

1 Like

Thanks For you response @Yoichi @ashwin.ashok I need in String Array as a result

I need to print the year also together with the month

Hi @Marian_B ,

Could you give this a try?
image

(From row In dt.AsEnumerable()
Let firsthalf = Convert.ToDateTime(String.Format("{0} {1} {2}","01",row("MONTH START").ToString,(CInt(row("YEAR END").ToString)-1).ToString),New System.Globalization.CultureInfo("ro-RO"))
Let secondHalf = Convert.ToDateTime(String.Format("{0} {1} {2}","01",row("MONTH END").ToString,row("YEAR END").ToString),New System.Globalization.CultureInfo("ro-RO"))
Let range = Convert.ToInt32(Math.Abs(DateDiff(DateInterval.Month, secondHalf,firstHalf)))
Let months = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(String.Join(",",Enumerable.Range(0,range+1).Select(Function(s) (firsthalf.AddMonths(s).ToString("MMMM yyyy",New System.Globalization.CultureInfo("ro-RO"))))))
Let ra = New Object(){row("MONTH START"),row("YEAR START"),row("MONTH END"),row("YEAR END"),"",months}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

DateIntervalInRomanian_v1.xaml (7.8 KB)

Kind Regards,
Ashwin A.K

1 Like

Hello @ashwin.ashok

Thanks you

I need it in Array of String

Hi @Marian_B ,

The requirement is not clear, I understand that you want in in an array, but how will you know what belongs to which line item?

Just trying to make it easier for you, I’ll edit the code accordingly.

Kind Regards,
Ashwin A.K

Hi @Yoichi & @ashwin.ashok

While looping through the excel it getting the first row value for all the other row too. check the XAML file while running

Here is the XAML file
Sample20220406-6.zip (63.6 KB)

Input and Output

Hi @Marian_B ,

I’ve modified the code to work in a For Each Activity, please have a look.
revised.zip (11.2 KB)

Kind Regards,
Ashwin A.K

1 Like