Find out the date by week start date and day

[Rupesh_Parle]

Hi People,

I want find out the date by considering week start date and day name where hours are greater than 0.

Attached screenshot contains 3 rows which has week no, week commencing date and then 7 columns for 7 days.

The logic would be to get dates where hours is greater than 0.
For eg., 1st row the output will be 26 Dec 2022 and 27 Dec 2022
2nd row output will be 26th to 30th Dec 2022.

Thanks in advance!

[Yoichi]

Hi,

Is the result of 2nd row 28 Nov 2022, 29 Nov 2022 … isn’t it?
If so, Can you try the following sample?

dt.AsEnumerable.SelectMany(Function(r) arrWeekDay.Select(Function(s,i) if( CInt(r(s).ToString())<>0,DateTime.ParseExact(r("Week Commencing").ToString,"dd MMM yyyy",System.Globalization.CultureInfo.InvariantCulture).AddDays(i).ToString("dd MMM yyyy"),""))).ToArray()

Sample20230814-1L.zip (7.4 KB)

Regards,

[Rupesh_Parle]

Hello,

Thank you so much, the output is as expected.

But when I am trying to execute the bot with my file, I am getting an error ‘Assign: String ‘12/25/2022 00:00:00’ was not recognized as a valid Date Time.’

As you can see in the screenshot the date is in ‘MM/dd/yyyy’ format. I tried to change the same in code but it is throwing an same error.

Regards,
Rupesh P

[Yoichi]

Hi,

For now, can you try to turn on PreserveFormat option?

Or use DateTime.Parse instead of DateTime.ParseExact

dt.AsEnumerable.SelectMany(Function(r) arrWeekDay.Select(Function(s,i) if( CInt(r(s).ToString())<>0,DateTime.Parse(r("Week Commencing").ToString).AddDays(i).ToString("dd MMM yyyy"),""))).ToArray()

Regards,

[Rupesh_Parle]

Hello,

Tried both, getting the below error.

[Yoichi]

Hi,

This message shows there is a blank cell in Week Commencing column. Can you try the following expression?

PreserveFormat : on

dt.AsEnumerable.Where(Function(r) not String.IsNullOrEmpty(r("Week Commencing").ToString)).SelectMany(Function(r) arrWeekDay.Select(Function(s,i) if( CInt(r(s).ToString())<>0,DateTime.ParseExact(r("Week Commencing").ToString,"dd MMM yyyy",System.Globalization.CultureInfo.InvariantCulture).AddDays(i).ToString("dd MMM yyyy"),""))).ToArray()

OR

PreserveFormat : off

dt.AsEnumerable.Where(Function(r) not String.IsNullOrEmpty(r("Week Commencing").ToString)).SelectMany(Function(r) arrWeekDay.Select(Function(s,i) if( CInt(r(s).ToString())<>0,DateTime.Parse(r("Week Commencing").ToString).AddDays(i).ToString("dd MMM yyyy"),""))).ToArray()

Regards,

[Rupesh_Parle]

Hi,

Thanks! It’s working as expected.

Is there any way to store the output in datatable variable?

Regards,
Rupesh P

[Yoichi]

Hi,

@Rupesh_Parle

Is there any way to store the output in datatable variable?

There are some ways to achieve it. For example, using GenerateDataTable or AddDataRow with ForEach etc. The following is a sample of GenerateDataTable.

Sample20230814-1Lv2.zip (7.5 KB)

Regards,