What is formula EDATE from Excel in Smartsheet
Answers
-
I learn from the best - you 😉
Thank you for helping me through...
-
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
-
@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?
-
yes exactly :)
-
or also copy the row with the new date below in the same table
-
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".
-
@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 :)
-
Happy to help! 👍️
-
@Paul Newcome THANK YOU SO MUCH for the solution! It worked perfectly for my project 😊
-
-
@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))
-
@Anya Brudzinska Are you able to provide some screenshots for context?
-
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?
-
@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))
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!