What is formula EDATE from Excel in Smartsheet

Joerg Schmidt

I learn from the best - you 😉

Thank you for helping me through...

Alexander Semke

Hi, I am trying something similar. Your formular looks powerful.

Actually I have an similar issue. Translated to your use case above. I want that if the "next audit date" is marked as "COMPLETE" it shall copy the row and change the date of "letzte Wartung" to the date of change and create a new "next date audit" with added Turnus.

I hope I could describe my problem clearly. About a feedback I would be very grateful.

Cheers, Alex

Paul Newcome

@Alexander Semke I'm not sure I follow. Here's what I am gathering...

You want to change a status of sorts to "COMPLETE". When that happens, you want to copy that row to another sheet, use the date of when it was marked as "COMPLETE" and then add x number of months to that?

Alexander Semke

yes exactly :)

Alexander Semke

or also copy the row with the new date below in the same table

Paul Newcome

@Alexander Semke

To keep it on the same sheet, you would need to manually enter the new row (unless you used a 3rd party app to insert such as Zapier).

Then the only modifications you would need to do to the above solution would be to use a DATEONLY function and reference the Created Date column instead of the regular date column.

Basically... Wherever you see

[letzte Wartung]@row

you would instead use

DATEONLY([Created Date Column]@row)

The same modifications would be used if you are copying to a new sheet.

You could then in turn use cross sheet formulas such as an INDEX/MATCH or VLOOKUP to pull the generated date from the copy sheet back to the original, but you would need to reset the status back to something other than "COMPLETE".

Alexander Semke

@Paul Newcome thank you very much for your fast respones :)

My plan was to make it completly automated, without reseting the status. But I think it will be to complicated afterwards. I think I just keep the manual part.

Thanks a lot for your tips :)

Paul Newcome

Happy to help! 👍️

Anya Brudzinska

@Paul Newcome THANK YOU SO MUCH for the solution! It worked perfectly for my project 😊

Paul Newcome

@Annamaria Brudzinska Happy to help. 👍️

Anya Brudzinska

@Paul Newcome hey, I came across an issue while verifying data and I cannot figure out how to fix it. How can I adjust the formula so it starts counting months including the purchase month? Right now, it starts counting from the following month after the purchase date, which is correct, but for my other calculations, I need it to consider a purchase date to be month first. For example, my purchase date is August, the life is 12 months, my end date calculates August the following year, and I need to stop it in July. Similar to 1 month or 60 months, I'm getting 2 and 61 months of data.

=DATE(YEAR([Purchase Date]@row) + ROUNDDOWN((MONTH([Purchase Date]@row) + [Prepaid Life if Applicable]@row) / 12, 0) + IF(IF(MOD(MONTH([Purchase Date]@row) + [Prepaid Life if Applicable]@row, 12) = 0, 12, MOD(MONTH([Purchase Date]@row) + [Prepaid Life if Applicable]@row, 12)) = 12, -1), IF(MOD(MONTH([Purchase Date]@row) + [Prepaid Life if Applicable]@row, 12) = 0, 12, MOD(MONTH([Purchase Date]@row) + [Prepaid Life if Applicable]@row, 12)), DAY([Purchase Date]@row))

Paul Newcome

@Anya Brudzinska Are you able to provide some screenshots for context?

Anya Brudzinska

@Paul Newcome

Sorry for the delay. Prepaid Life is equal to 1 month, so it should be only distributed among 1 month, not both (another, separate calculation I'm doing). If you look at the 12 months option, it is from September to September, not September to August. The amount is distributed among 13 months, not 12.

Alternatively, and probably easier way, I can add another column and subtract 1 month from the end date column. However, it gets tricky when needed to subtract 30 days from March 1, because then it jumps to January. Is there a way around this?

Paul Newcome

@Anya Brudzinska Try this...

=DATE(YEAR([Purchase Date]@row) + ROUNDDOWN((MONTH([Purchase Date]@row) + ([Prepaid Life if Applicable]@row - 1)) / 12, 0) + IF(IF(MOD(MONTH([Purchase Date]@row) + ([Prepaid Life if Applicable]@row - 1), 12) = 0, 12, MOD(MONTH([Purchase Date]@row) + ([Prepaid Life if Applicable]@row - 1), 12)) = 12, -1), IF(MOD(MONTH([Purchase Date]@row) + ([Prepaid Life if Applicable]@row - 1), 12) = 0, 12, MOD(MONTH([Purchase Date]@row) + ([Prepaid Life if Applicable]@row - 1), 12)), DAY([Purchase Date]@row))

Gajapathi Muniyappa

Paul, Thanks for the formula, It is working for Plus 1 month, 2 month 3 month like that and minus 1 month also but when I tried for minus 2 months it is working only when month falls under Feb but it is not working for Jan. Could you please help me out on this.

=DATE(YEAR([First Country FPI]@row) + ROUNDDOWN((MONTH([First Country FPI]@row) - 2) / 12, 0) + IF(IF(MOD(MONTH([First Country FPI]@row) - 2, 12) = 0, 12, MOD(MONTH([First Country FPI]@row) - 2, 12)) = 12, -1), IF(MOD(MONTH([First Country FPI]@row) - 2, 12) = 0, 12, MOD(MONTH([First Country FPI]@row) - 2, 12)), DAY([First Country FPI]@row))