What is formula EDATE from Excel in Smartsheet
Answers
-
I want to use the edate function from excel in smartsheet. I want to take my current date ("date of execution" column) and add 14 months (my "duration") column. in excel it would simply look like this =EDATE(D5,E5)
why wont this work in smartsheet?
-
@Melanie Pink Adding 14 months would look something like this...
=IFERROR(DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) + 2, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 2, MONTH([Date Column]@row) - 10, DAY([Date Column]@row)))
-
@Paul Newcome Hi Paul,
hoping you can help me
=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, DAY([Date Column]@row)),
DATE(YEAR([Date Column]@row) + 1, 1, DAY([Date Column]@row)))
I'm trying to use this formula but can't get the second part of the formula working.
Im using "-3" to work out a within 3 months range for this date column
how do i configure the second part of the formula to counter the invalid value fields
please can you assist me
-
@Paul Newcome Hi, why did you put -10 after month section please
I am trying to understand the function here but not really sure
-
@Maz Uddin Basically the first DATE function adds the number of months. When that number goes beyond 12 it becomes as issue as there are no month numbers greater than 12. That is where the second DATE function comes into play. We can take 12 of those months and move then over to the the YEAR + 1 then add in the remaining months.
In your particular case you are wanting to use 3 months. That means the second date function would use 9 since 12 - 3 = 9.
-
Hello @Paul Newcome. I am trying to calculate future month due dates based on a past service date. All of these dates are the last day of the month and am calculating out 3,6,12 months. I have looked at many of these threads where you have offered solutions and I have gotten those to work for my purpose except for the fact that when the total days in the month differ, the formulas round to the first day of the next month (e.g. 3 months from Aug 31 = Dec 1). Have you or others figured out a way to prevent this using formulas?
-
@PEARCEMI You would want to add one more month to the total and set the day portion of the DATE function to 1 (1st day of the next month). You would then subtract 1 day from this.
=DATE(year_portion, month_portion + 1, 1) - 1
-
Thanks @Paul Newcome. I used your strategy to improve several formulas for day, month and year projections.
-
Hi, I have an employee data sheet with columns, Date Hired, and Years of service. How do I calculate their next Years of Service anniversary date which is acknowledged every 5 years (5, 10, 15, 20 etc)
-
@Yanie Try this:
=DATE(YEAR([Date Hired]@row) + CEILING(YEAR(TODAY()) - YEAR([Date Hired]@row), 5) + IF(TODAY() > DATE(YEAR([Date Hired]@row) + CEILING(YEAR(TODAY()) - YEAR([Date Hired]@row), 5), MONTH([Date Hired]@row), DAY([Date Hired]@row)), 5, 0), MONTH([Date Hired]@row), DAY([Date Hired]@row))
-
@Paul Newcome hi try the formula but its not working
-
Hi @Yanie
It would be helpful to have more information in order to help - what didn't work about the formula? Are you receiving an error or an incorrect result? Can you post a screen capture of it in your sheet (but block out sensitive data).
Thanks! Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. this is the result.
-
Hi @Yanie
That formula indicates that the cell it's looking at (the one that says 19/05/21) does not contain a Date value.
Can you check to see if your "Date Hired" column is a Date column? If it is, then how is the information being input into that cell?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. I try it both "Date Hired" column is a Date column and not. same INVALID DATA TYPE. I just want a formula that can show who will turn 5,10,15 years in the service.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!