IF Statements when a score / number is between two values
Comments
-
Ah. Ok. I see what you are wanting to do. I am going to suggest a conversion of the full time into a number to start.
=VALUE(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row) - (FIND(" ", Modified@row) + 1))) + IF(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row) - (FIND(" ", Modified@row) + 1)) <> "12", IF(FIND("P", Modified@row)> 0, 12), IF(FIND("A", Modified@row)> 0, -12)) + (VALUE(MID(Modified@row, FIND(":", Modified@row) + 1, 2)) / 60)
This should give you the hour in 24 hour format and the minutes as a decimal.
Examples:
7:00 AM = 7
7:00 PM = 19
4:30 PM = 16.5
Because it is a longer formula and you are going to want to use the results in a nested IF, I suggest leaving it in it's own column and referencing this [Time Conversion] column in the IF formula. We also know that every entry will end with " TRUCK" so we don't have to type that out in every single IF output. We can just tack that onto the end after the IFs are closed.
=IF([Time Conversion]@row < 7, "7AM", IF([Time Conversion]@row < 8, "8AM", IF([Time Conversion]@row < 9, "9AM", ........................................................................))))))))))))) + " TRUCK"
.
The other option that may allow for some flexibility and easier management if you plan to expand/change your options would be to build out a table similar to
Hour..........Truck
7................7AM TRUCK
8................8AM TRUCK
.
.
.
.
22.5..........10:30PM TRUCK
Then you would use a formula along the lines of:
=INDEX({Table Truck Column}, MATCH(MIN(COLLECT({Table Hour Column}, {Table Hour Column}, @cell > [Time Conversion]@row)), {Table Hour Column}, 0))
Now all you have to do is adjust the table and not worry about editing any formulas if anything needs to change with the outputs.
-
@Paul Newcome That worked! thank you so much! If I could just figure out a way to have them show as a blank cell until something was submitted that would be great! lol
-
@Desirae Moore I'm not sure what you mean... The formulas shouldn't be in a row that hasn't had a form submitted yet.
-
Sorry let me show you the columns I'm referring to.
-
@Paul Newcome Is it because of me dragging the formula down to each cell in the column?
-
You should be able to set it as a column formula so that it auto-populates as new forms are added.
-
Hello @Paul Newcome ,
could you help me please? How can i use symbols of 4 different colors for the following scenarios?
There are 2 parts;
part 1; contract notice period,
part 2; contract expiry time
for part1;
If the day remaining until the notice of termination is over 60 days; Green
If it is between 60 - 15 days; blue
If between 15 and the termination notice period;
Yellow
If the notice period for termination has passed; Red
and for part2;
if the days remaining until the end of the contract is over 60 days; Green
If it is between 60 and 30 days; blue
If it is between 30 and 0 days; yellow
if the contract is delayed; Red
-
@matakan If you could, please start a new thread as your question isn't quite in line with the topic of this particular thread.
-
@Paul Newcome Hey I could really use your help :( again lol So for some reason the smartsheet you helped me with earlier is now on the fritz! here is the formula I was using also a screen shot of what's showing.
=IF([time conversion]@row < 8, "8AM", IF([time conversion]@row < 9, "9AM", IF([time conversion]@row < 10, "10AM", IF([time conversion]@row < 11, "11AM", IF([time conversion]@row < 12, "12PM", IF([time conversion]@row < 1, "1:30PM", IF([time conversion]@row < 2, "2:30PM", IF([time conversion]@row < 3, "4PM", IF([time conversion]@row < 4, "4PM", IF([time conversion]@row < 5, "5:30PM", IF([time conversion]@row < 6, "7:30PM", IF([time conversion]@row < 7, "7:30PM", IF([time conversion]@row < 8, "8:30PM")))))))))))))+"TRUCK")
-
@Desirae Moore The final closing parenthesis should be before the +.
=IF(........................................)))))))))))))) + " TRUCK"
-
@Paul Newcome Ok so I changed that however what I noticed is after 12pm is where it goes bad lol it's good all the way up until 12pm and then as soon as it's 13(1pm) that's when the formula stops working
-
-
@Paul Newcome Hey Paul, It's me again lol. I was wondering if you could help me solve the same problem as before. I'm not sure what happened it was working just fine and now, again, after 12pm it stops working. Below is the formula and a screenshot.
-
@Desirae Moore Are you able to share the formula?
-
@Paul Newcome Anything 12pm and before will show (see screen shot below) anything after shows as the screen shot I sent you before.
=IF([time conversion]@row < 7, "7AM", IF([time conversion]@row < 8, "8AM", IF([time conversion]@row < 9, "9AM", IF([time conversion]@row < 10, "10AM", IF([time conversion]@row < 11, "11AM", IF([time conversion]@row < 12, "12PM", IF([time conversion]@row < 13, "1:30PM", IF([time conversion]@row < 14, "2:30PM", IF([time conversion]@row < 15, "4PM", IF([time conversion]@row, <16, "5:30PM", IF([time conversion]@row < 17, "5:30PM", IF([time conversion]@row < 18, "7:30PM", IF([time conversion]@row < 19, "7:30PM", IF([time conversion]@row, <20, "8:30PM"))))))))))))) + " TRUCK")
