How to create a formula that only affects part of the column...

JJLewis

For the days that are marked DBL in the Time column, we are trying to adjust the formula in the Arrival Time column so that only the first 4 rows are 1.5 hours before the time listed in the Time column and the rest of that day is 1 hour before the listed time...i.e.. 08:15 = 06:45 (1.5 hours) and 08:30 = 07:30 (1 hour). The current formula used in the Arrival Time column is...

"=IF(lvl@row = 0, " ", VLOOKUP(Time@row, {Time Range 1}, 5, false))"...

Below are the screen shots for the Surgery Scheduling sheet we are using as well as the reference sheet with the times to post in the Arrival Time column...

Thank you in advanced...

Gia Thinh

Hi @JJLewis,

It seems we need to index rows in your sheet to determine RowID, then setup formulas depend on their RowID.

First, create RowID column with a formula :

=COUNTIF(Date$1:Date@row, Date@row) - 1

If RowID<=4 : use 1.5 hours before the time listed in the Time column, else 1 hour

Then modify your formula in the Arrival Time column as below:

=IF(lvl@row = 0, " ", IF(RowID<=4, VLOOKUP(Time@row, {Time Range 1}, 5, false), VLOOKUP(Time@row, {Time Range 1}, 6, false)))

Hope that helps.