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

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...
Best Answer
-
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.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Answers
-
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.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!