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

Help Something Else
#1

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)

image
image1418×214 8.56 KB

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

Thanks in advance

1 Like
#2

Hello @Marian_B ,

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

Thanks!
Athira

#3

Hello @athira.somasekharan

Thanks for the quick respond

Yes may be in future it will come

#4

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
#5

Hi,

Another solution:

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

img20220406-a1
img20220406-a11130×560 35 KB 

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
#6

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

image
image855×197 3.75 KB

#7

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
#8

Hello @ashwin.ashok

Thanks you

I need it in Array of String

#9

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

#10

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

image
image1435×262 9.02 KB

#11

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