Best Practice
Best Practice
Review best practices, tips and tricks, and more from fellow Community members.
Formulas for Calculating Time
Comments
-
First off, you are always so helpful! Thank you! I got those two formulas to work so I am able to convert the time to a number. The next issue I am running into is the next formula that I originated these questions.
This is the formula in excel: =IF(AT15="",0,IF(AT15<=TIMEVALUE("12:30"),15.5,IF(AT15<=TIMEVALUE("13:00"),11,IF(AT15<=TIMEVALUE("13:30"),6.5,IF(AT15<=TIMEVALUE("14:00"),4,IF(AT15<=TIMEVALUE("14:30"),2,IF(AT15<=TIMEVALUE("15:00"),0.5,IF(AT15>=TIMEVALUE("15:00"),"N/A"))))))))
I have replaced the AT column with the column that replaced the time with the number. The forumula calculates, but it isn't matching the value that excel gives me. I think something is off with my smartsheet formula. Here is the formula I have been trying to use:
=IF([VVBS Closed Time]@row = "", 0, IF([VVBS Closed Time]@row <= ("12.5"), 15.5, IF([VVBS Closed Time]@row <= ("1"), 11, IF([VVBS Closed Time]@row <= ("1.5"), 6.5, IF([VVBS Closed Time]@row <= ("2"), 4, IF([VVBS Closed Time]@row <= ("2.5"), 2, IF([VVBS Closed Time]@row <= ("3"), 0.5, IF([VVBS Closed Time]@row >= ("3"), "N/A"))))))))
Thank you for the help!
-
@Megan Harry When you put numbers inside of quotes it is actually looking for a text string instead of a numerical value. We also don't need the parenthesis around each of the numbers.
=IF([VVBS Closed Time]@row = "", 0, IF([VVBS Closed Time]@row <= 12.5, 15.5, IF([VVBS Closed Time]@row <= 1, 11, IF([VVBS Closed Time]@row <= 1.5, 6.5, IF([VVBS Closed Time]@row <= 2, 4, IF([VVBS Closed Time]@row <= 2.5, 2, IF([VVBS Closed Time]@row <= 3, 0.5, IF([VVBS Closed Time]@row >= 3, "N/A"))))))))
-
Thank you! That makes sense, but I am still having trouble getting the formula to populate the correct numerical value. Some of them are populating correctly, but some aren't and I can't figure out why.
For example, say the time is 1.83 the solution should be 4, but it is giving me 15.5 and same thing when the time is 1.5. It gives me 15.5 when it should be 6.5, and when I type in 2.17 it still gives me 15.5 instead of 2. The only ones that are working are IF([VVBS Time Calc]@row >= 3, "N/A" and anything before 12.5.
Any suggestions or ideas? I have been starring at these formulas for too long. I am so thankful for the help!
-
@Megan Harry My apologies for missing this before. That problem stems from the order of your IF statement. Nested IFs stop on the first true value. If the time is 1.83, then that will trigger a true for the second IF of less than or equal to 12.5. You will need to rearrange your IF statements to flow accordingly.
-
Thank you that makes so much sense. I feel like I am so close to getting it, but the last thing I can't figure out is where to put the =IF([VVBS Time Calc]@row >= 3, "N/A" in the formula. All of the other IF statements are equal to or less than and this one is equal to or greater than.
This is where I am thus far:
=IF([VVBS Time Calc]@row = "", 0, IF([VVBS Time Calc]@row <= 1, 11, IF([VVBS Time Calc]@row <= 1.5, 6.5, IF([VVBS Time Calc]@row <= 2, 4, IF([VVBS Time Calc]@row <= 2.5, 2, IF([VVBS Time Calc]@row <= 3, 0.5, IF([VVBS Time Calc]@row <= 12.5, 15.5, IF([VVBS Time Calc]@row >= 3, "N/A"))))))))
Any suggestions? Thank you again!
-
Maybe because your last IF can be resolved in the previous if statement, the <= 12.5.
Are you testing it and it is not working? What number are you testing with? A number less that 12.5?
-
@Megan Harry Taking a closer look, you actually have 3 conflicting arguments here.
Less than or Equal To 3
Greater than or Equal To 3
Less that or Equal to 12.5
The number 3 could trigger a "true" for all three of these. You are going to need to figure out how you want to resolve these arguments so that they are not overlapping like that.
-
Alright, I think I have solved my IF statement issue, but while doing that I realized that my formula for converting time to a number is wrong. Some of the calculations are correct, but some are wrong. For example, when I type in 3:30 PM the calculation gives me 15.50 which is correct, but when I type in 2:00pm it gives me 2.00 when it should give me 13.00. I have copied my formula below. I have also attached a screen shot so you can visualize what I am saying.
=VALUE(MID([VVBS Closed Time]@row, FIND(" ", [VVBS Closed Time]@row) + 1, FIND(":", [VVBS Closed Time]@row) - (FIND(" ", [VVBS Closed Time]@row) + 1))) + IF(VALUE(MID([VVBS Closed Time]@row, FIND(" ", [VVBS Closed Time]@row) + 1, FIND(":", [VVBS Closed Time]@row) - (FIND(" ", [VVBS Closed Time]@row) + 1))) <> 12, IF(FIND("P", [VVBS Closed Time]@row) > 0, 12), IF(FIND("A", [VVBS Closed Time]@row) > 0, -12)) + VALUE(MID([VVBS Closed Time]@row, FIND(":", [VVBS Closed Time]@row) + 1, 2)) / 60
Thank you so much!!
-
I figured out the answer to my question above! It was the way I was typing the AM, PM. It is very specific! Now it looks like my IF statement is still off so back to figuring that out.
Thank you!
-
@Megan Harry My apologies for the delay.
The FIND function is case sensitive, so if you are searching for "A", it will not find "a".
What is still "off" with your IF?
-
Sorry I am just now seeing your most recent reply. I figured out my formula! I just had to rework my arguments a bit more, but finally got it.
Thank you for your help!!
-
@Megan Harry Glad you were able to get it squared away. Happy to help. 👍️
-
I am having an issue with this formula not returning the correct time when the minutes portion is under 10 minutes. I'm not sure what I'm doing wrong. I am using LEFT and RIGHT functions to extract time and date from my system column. From there I have the following formulas:
Time Helper: =(VALUE(LEFT([Time Extract]@row, FIND(":", [Time Extract]@row) - 1)) + IF(CONTAINS("P", [Time Extract]@row), IF(VALUE(LEFT([Time Extract]@row, FIND(":", [Time Extract]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Extract]@row, FIND(":", [Time Extract]@row) - 1)) = 12, -12))) + (VALUE(MID([Time Extract]@row, FIND(":", [Time Extract]@row) + 1, 2)) / 60)
Time: =INT([Time Helper]@row) + ":" + ([Time Helper]@row - INT([Time Helper]@row)) * 60
The formula works fine for the 2:18 PM time, but for the 3:05 and 3:06 PM times, it drops the 0 in the Integer formula. Is there a fix for this??
-
@Leslie Merlino Try this in the Time column:
=INT([Time Helper]@row) + ":" + IF(([Time Helper]@row - INT([Time Helper]@row)) * 60 < 10, "0") + ([Time Helper]@row - INT([Time Helper]@row)) * 60
-
@Paul Newcome Hello Paul, I posted this on the Community but posting it here as well since it looks like this thread is mostly about time. I am trying to get an average of time (down to seconds) spent taking a call for a date range. For example, if A spent 0:00:20 on one call, 0:00:35 on next, 0:01:04 on next, etc, what would be the average of the time spent answering these calls.
I started out by using decimals instead of actual time (so 0.20, 0.35, 0.04) but when you take an average of the decimal, it gives a different avg than when you do it with actual time.
Hoping you can provide some guidance?
Thanks so much in advance!
