Subtracting Days from a Date

Taylor Lannon

Hello,

I am trying to subtract 30 days from a column pulling in dates from another sheet using formulas and it keeps yielding an #INVALID OPERATION error. I have already checked and do not have dependencies enabled. Both columns are formatted as date columns. While playing around I noticed when I add the days it instead tacks the number to the end of the year. How do I correct this?

Formula/function needed but causing error: =[Target Activation Date]@row - 30

Formula discovered just tacking number onto the end of the year instead of adding days: =[Target Activation Date]@row +30 (Picture for reference)

30

Paul Newcome

The current formula will pull date values if the source data is date values.

If one of the columns is not showing the calendar (when no formula is applied), it is not set as a date type column.

How is the source data being populated?

=Chris Palmer

Hello,

Have you ensured the Column Type is a Date?

This option is visible by hovering over the column and clicking the 3 dots.

Taylor Lannon

Yes I have already verified the column type for both columns is date.

Paul Newcome

What are the formulas populating the date columns?

ker9

Hi @Taylor Lannon

I suspect that the date is entered as text - example in Date2 Column, the first date is text (in a date column) and the second is a date.

Another clue that it is not a date: when clicking on the calendar icon in the text date cell, it does not go to the date shown, it shows today's date:

Formulas to convert the text to date will depend on how consistent the data is. Do all the dates use 2 digits for month, date and year (01/01/01 vs 1/1/01).

Taylor Lannon

https://community.smartsheet.com/discussion/comment/411474#Comment_411474

https://community.smartsheet.com/discussion/comment/411472#Comment_411472

The formula for the Target Activation Column is =IFERROR(INDEX({Automations Target Activation Date}, MATCH(MCC@row, {Automations MCC #}, 0)), "")

I did notice that while I restricted both columns to date only, the calendar option does not display on the target activation column. The data is always going to be in the mm/dd/yy format. Thank you for the tip on using a formula to convert text to date. Is there a way to modify my existing formula to ensure it is read as a date?

Paul Newcome

The current formula will pull date values if the source data is date values.

If one of the columns is not showing the calendar (when no formula is applied), it is not set as a date type column.

How is the source data being populated?

Taylor Lannon

This was the solution thank you! The original source from the other sheet was not set to date, it was set as text. I adjusted and the error has disappeared and the correct date is populating instead. Thanks again!!

Paul Newcome

Happy to help. 👍️