If difference between 2 dates follows criteria then delete values

[Gordon85]

Hi

I have this input file (please see below picture and attachment)

I have this code:

Please Login or Register  to view this content.

Alternatively could this be done when the difference between 2 dates is <2 days to delete the entire above row where the difference occurs?

Below is the Input, the Yellow cells (the above cells where the difference between 2 dates is <2 days) are expected to be deleted because the Red cells indicate a difference <2 days between 2 dates
Input.JPG


Output of the code (please see below) is a bit faulty as it adds "delete this" on both rows where the date difference is <2 days. It should "delete this" just on the top row where the difference occurs.


Output.JPG


I added the "delete this" instead of blank value just for the sake of being more visible.

Alternatively, the whole top row where the difference occurs could be deleted if it would be easier.



Many thanks in advance.
All the best,
Gordon

[Bernie Deitrick]

Your data does not lend itself to actually figuring out what you want - you need to have the date of the rental, and the date of the return. Saying it was rented on Wednesday and returned on Thursday is meaningless if the Thursday is the next week instead of the next day. Your "code" compares the value to Date, which is today, and does nothing else.

[Gordon85]

Hello and apologies.

I updated post #1 with images and code.

If I replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

it just shifts the delete range but it still deletes values from 2 rows such as that, deletes the top row where the difference occurs (which is good), but also the row above it (which is bad)

The code has to go through all the column B and detect if the difference between the 2 consecutive dates (i, i+1) is <2 days, if it is so, then delete the value of the C cell on the associated i row, or alternatively delete just the entire i row (top row) if it is easier.


Any help would be appreciated.

Thank you!
Gordon

[Gordon85]

Solved as per below:

Please Login or Register  to view this content.

[Bernie Deitrick]

Here is an alternative that deletes the entire row - but only if A is the same (just in case)

Please Login or Register  to view this content.

This version ignores what't in A

Please Login or Register  to view this content.

[Gordon85]

Thank you, appreciated! It works! Rep+