Check consecutive dates and set a flag

Aspose.Cells Product Family
1

Hello


I am trying to generate the “Result” column for each product based on the difference in the dates.

The logic is as follows:

For each product, check the difference between each consecutive dates. If it is more than 5 then set the Result flag to 1 else set it to 0.

Secondly all the last dates for each product should be set to 0.




Thanks
2

Any suggestions/solutions as this is urgent?


3

Hi,


Thanks for your posting and using Aspose.Cells.

Your question has two parts. One is related to Aspose.Cells and other is related to Algorithm. We are afraid, you will have to write the Algorithm yourself.

However, for the part that is related to Aspose.Cells, please see the following code. The code reads the cells B2 and B3 dates, subtracts them and if the difference is more than 5 days, it sets the value of cell D2.

I have attached the output excel file generated by this code for a reference.

C#
//Load your workbook
Workbook wb = new Workbook(“ProductList.xlsx”);

//Access your worksheet
Worksheet ws = wb.Worksheets[0];

//Access your required cells
Cell b2 = ws.Cells[“B2”];
Cell b3 = ws.Cells[“B3”];
Cell d2 = ws.Cells[“D2”];

//Access dates from cells B2 and B3
System.DateTime dt1 = b2.DateTimeValue;
System.DateTime dt2 = b3.DateTimeValue;

//Subtract one date from another
TimeSpan span = dt2.Subtract(dt1);

//Get difference in days, we want absolute values
int days = Math.Abs(span.Days);

//If difference is more than 5 days, then put value in D2
if (days > 5)
d2.PutValue(1);

//Save the output excel file
wb.Save(“output.xlsx”);

4

Hi,


Thanks for using Aspose.Cells.

Another good solution for your problem is to make use of Excel formulas. For example, you can use this formula i.e.

=IF(B2-B3>5,1,0)

I have attached the sample excel file as well as its screenshot for your reference.

5

Thanks for the suggestions.