Formula to add a # of days to a date to calculate ETA
I have a form that calculates a TAT based on the amount of material requested (1-2 items is 4 days, 2-5 is 6 days, etc etc). I also have a column that is a date only version of the time stamp, so the requester does not have to enter "Date of Request" inthe form, it defaults to the date of the request this way. What I want to do is create a formula that add the TAT to the date of the request, to give an estimated date of when the items can be received. When I try =SUM([Date]@row, [TAT]@row) the cell is blank even though both columns have data in them. It doesn't even give me the typical #UNPARSEABLE (or similar) to indicate that the formula isn't working, it's just blank. Any thoughts on what I'm doing wrong or if what I'm trying to do is impossible?
Answers
-
Try just using basic addition.
=[Date of Request]@row + TAT@row
-
Thanks @Paul Newcome . I did try that at first but it did not work. It basically just adds the # to the end of the date. Any other ideas? I'm so confused!
-
You date columns needs to be formatted as dates not text
-
Yes. Make sure that your dates are actual dates and your numbers are actual numbers as opposed to text values that just look like dates/numbers.
I venture to say it may actually be the numbers that are the issue. They are left justified which generally means text values. Numerical values are right justified. How are you populating the TAT column?
-
Thanks for the responses both Pauls! The date columns are already formatted as date columns. The TAT column is a text/number column; I'm not sure how to make it number only. The TAT column has a formula that calculates the TAT based on the volume of material requested (i.e. larger volumes take longer time). So maybe that is my problem. I tried making a helper column for the TAT that just pulled the value but that didn't fix the problem. Any additional advice is much appreciated!!
-
What is the exact formula in the TAT column?
-
=IF([Desired Amount]@row = "1-2 Liters", "20", IF([Desired Amount]@row = "3-5 Liters", "40", IF([Desired Amount]@row = "> 5 Liters", "60")))
-
Try removing the quotes rom around those number outputs. The quotes make them text values. Without quotes will output a numeric value.
-
Yes! That was the problem, I didn't know quotes around #s made them text. Formula working now, thank you so much for your help!!
-
Happy to help. 👍️
-
I'm using the simple calculation above to add 17 days to my start date and place the actual date in the End date column but I am getting #Date Expected as the result. What am I doing wrong?
Rhonda
-
@Rhonda Franklin Exactly what formula are you using? That error indicates that your formula is attempting to output a text string into a date type column. That leads me to believe that either your starting date column is not set as a date type / not storing date values and/or the number you are trying to add to the date is not being stored as a number.
-
Here is my formula. I have double checked and all of the columns are formulated as dates. The window column is set as text/number. I would like the 1 Week Window ENDS to be a date that is 17 days after the 1 week window.
-
@Rhonda Franklin Try just a basic addition instead.
=[1 Week Window STARTS]@row + Window@row
-
Hi, I have a similar issue, but I am looking to add 6 months to a date of employment to be the due date for my process. For example, if the DOE of an employee is 1/1/2002 the due date will be on 7/1 of the current year.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!