Time calculation formula using an IF variable

Joseph Briol

I am trying to write a formula that counts the number of days we left to finish specific work cases. The end result that I'm trying to calculate is the number of days remaining to work a second level case based on two variables: 1.) how much time is allowed for a specific case type, and 2.) how much time was exhausted completing the first level case.

My formula for calculating 1.) was a simple IF statement =IF([Pre-service or Post-service]1 = "Pre-service", "10", "30")

My formula for calculating 2.) was also a simple subtraction formula =[Level 1 Case Outcome Letter Date]1 - [Level 1 Appeal Receipt Date]1

When I try to take the next step and subtract the result of formula #2 from formula #1, I get an #INVALID OPERATION message. Is this because the IF statement didn't calculate into days?

I'd appreciate anyone's thoughts on this. Thanks

Paul Newcome

The problem is in your first formula. Using the quotes around the numbers will produce a text string that just looks like a number instead of an actual numerical value.

Try removing the quotes from around 10 and 30 and see if that gets things working for you.

Paul Newcome

The problem is in your first formula. Using the quotes around the numbers will produce a text string that just looks like a number instead of an actual numerical value.

Try removing the quotes from around 10 and 30 and see if that gets things working for you.

Joseph Briol

Thank you! That did the trick.

Paul Newcome

Excellent! Happy to help! 👍️

Medical jem

its 2020 and you're still suggesting manual methods? my suggestation is try new automated tools like https://dremployee.com/time-duration-calculator.php or any other, there are hell lot free tools on internet that you can try and save your time

Paul Newcome

@Medical jem I think the issue with most is that they can't be used inside of a Smartsheet. The solution above is more for a running automation. Using most 3rd party tools, you would have to manually enter your dates/times to have the calculations run then manually enter the result in the sheet. This would have to be done for every single row, but using methods such as the above posting will allow you to set it up once then take advantage of autofill so that new rows and updated dates/times are automatically calculated.