Time calculation formula using an IF variable
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
Best Answer
-
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.
Answers
-
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.
-
Thank you! That did the trick.
-
Excellent! Happy to help! 👍️
-
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
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!