IF Statements when a score / number is between two values

Paul Newcome

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.

Desirae Moore

@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

Paul Newcome

@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.

Desirae Moore

Sorry let me show you the columns I'm referring to.

Desirae Moore

@Paul Newcome Is it because of me dragging the formula down to each cell in the column?

Paul Newcome

You should be able to set it as a column formula so that it auto-populates as new forms are added.

marcusben

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

Paul Newcome

@matakan If you could, please start a new thread as your question isn't quite in line with the topic of this particular thread.

Desirae Moore

@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")

Paul Newcome

@Desirae Moore The final closing parenthesis should be before the +.

=IF(........................................)))))))))))))) + " TRUCK"

Desirae Moore

@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

How so?

Desirae Moore

@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.

Paul Newcome

@Desirae Moore Are you able to share the formula?

Desirae Moore

@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")