Formula to add a # of days to a date to calculate ETA

bgoodman

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?

Paul Newcome

Try just using basic addition.

=[Date of Request]@row + TAT@row

bgoodman

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!

Paul H

You date columns needs to be formatted as dates not text

Paul Newcome

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?

bgoodman

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!!

Paul Newcome

What is the exact formula in the TAT column?

bgoodman

=IF([Desired Amount]@row = "1-2 Liters", "20", IF([Desired Amount]@row = "3-5 Liters", "40", IF([Desired Amount]@row = "> 5 Liters", "60")))

Paul Newcome

Try removing the quotes rom around those number outputs. The quotes make them text values. Without quotes will output a numeric value.

bgoodman

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!!

Paul Newcome

Happy to help. 👍️

Rhonda Franklin

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

Paul Newcome

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

Rhonda Franklin

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.

Paul Newcome

@Rhonda Franklin Try just a basic addition instead.

=[1 Week Window STARTS]@row + Window@row

Kayla H.

@Paul Newcome

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.