Formula For reading a Value and then changing a date

[thebigbadbull55]

I am looking to create a formula to read a Y/N value in a spreadsheet and if Y, do nothing. If N, I need to update a date in the data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Available?[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]2019-01-01[/TD]
[TD]2019-01-02[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]2019-01-01[/TD]
[TD]2019-01-02[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Tiffany[/TD]
[TD]2019-01-01[/TD]
[TD]2019-01-02[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Jen[/TD]
[TD]2019-01-02[/TD]
[TD]2019-01-03[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]


So looking at the table, I need a formula that will read the available column and change a date. So if the available column reads Y, the formula should do nothing. If the available column reads N, I need to update the Start and End date to a date in the future (could simply be 2099-12-31) - don't want the system to pick up the date.

I have struck out trying to get anything working and it is driving me crazy. Not sure if it will use a different formula like SWITCH within an IF formula?

Any help would be much appreciated!!!

thank you!!

 

[mole999]

what drives your logic for the future date?

 

[thebigbadbull55]

This dataset is being utilized in third party software. I am trying to put the date far in the future so that the system won't read the entry.

 

[mole999]

so how often will you run the code and how far in the future ?

 

[thebigbadbull55]

Will be running everyday.

 

[mole999]

still need to know how far you would push it

 

[mole999]

very loosley

Dim N as long
    For N = 1 To 100
    'Target review column S
    'U is your date value
   If Range("S" & N) = "N" Then Range("U" & N) = Range("U" & N)+100
   next n

untested