Formula to calculate two week iteration
Our company works in two week chunks we call iterations. Using the following formula in Excel I am able to calculate what the current iteration is. However when I import from Excel Smartsheet doesn't appear to like the formula.
Using the current date the formula in Excel is
=FLOOR.MATH(DAYS(NOW(),"6/11/01")/14)
Using a specific date the formula in Excel is
=FLOOR.MATH(DAYS("8/2/20","6/11/1")/14)
Does anyone know of a way to get these formulas to work?
Answers
-
In Smartsheet, you'll want to use FLOOR instead of FLOOR.MATH and NETDAYS instead of DAYS.
There is now Smartsheet version of NOW as it does not have time formulas, you could try using TODAY() which returns the current day.
Also, if you put the date in quotes it will act like a string. You can either reference a cell in a date column, or use the DATE formula.
I'm not sure the multiple you want in your FLOOR formula, so I've removed that part.
To get the net days between today and 6/11/01, the formula is
=(NETDAYS(TODAY(), DATE(2001, 6, 11)))
It returns -6997, if you divide by 14 you get -499.78571
=(NETDAYS(TODAY(), DATE(2001, 6, 11))) / 14
I'm assuming you want a whole number? If so, this returns -500
=ROUND(NETDAYS(TODAY(), DATE(2001, 6, 11)) / 14, 0)
Your second formula in this style is
=ROUND(NETDAYS(DATE(2020, 8, 2), DATE(2001, 6, 11)) / 14, 0)
Though, it's sometimes easier to reference date columns, as in the below screenshot
If you want the values to not be negative, change the order of the dates.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!