Date calculating

Hello everyone,

I have a question regarding the calculation of dates:
I have structured report data I import into a datatable which is formatted like this:

I am wanting to perform the following calculations:

  1. Starting from the record with the oldest Start Date (C5), calculate whether the End Date (D5) is separated from the Start Date of the next record (C4) by only a weekend (Sat + Sun).
  2. If date is separated by a weekend AND the number in the E4 is more than in E5, then delete Row5.
  3. If End Date (D5) is separated from the Start Date of the next record (C4) by more than a weekend e.g. 3 or more days then do not delete the row5.
  4. Take no action with rows with an end date of 31.12.9999.

The functions need to be dynamic as some reports will have 1 row and some will have 10+.

I know I am asking for a lot of help here but I am stuck. I figured I would need multiple nested IF functions but I don’t really know where to start.

Thank you so much!

Hello @Clintonnn

What if there are two rows, the first row has end date 31.12.9999 (it won’t be deleted), but the diff between the 2nd row’s end date and 1st row’s start date is equal to a weekend and the amount of 1st row is greater than 2nd row. Will the second row be deleted?

1 Like

Hi @Clintonnn

You may consider reading the excel as data table whereas column C and D datatype would-be date then you can perform logical operations =, >=, <=, etc. to ensure your business logic matches before you can perform delete row operations.

Please refer to this forum post by Sai Kiran where he has shared a sample workflow as well. Changing Column Type to Date Format - #9 by sai.kiran

Let us know if you need any further details. :call_me_hand:

If you find this information useful then mark this as the solution and hit like icon.

Happy automation, Cheers!
Subhas Malik

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.