Help Workday Calculcations
Hello,
I try to put a date in a column 5.5 months before the end of my contract.
With the DATE function, it gives an INVALID VAL code for some dates and if I try with the WORKDAY function, it gives an INCORRECT code.
=IFERROR(IF([Fin contrat]@row >= TODAY(); DATE(YEAR([Fin contrat]@row); MONTH([Fin contrat]@row) - 5; DAY([Fin contrat]@row) - 15)); DATE(YEAR([Fin contrat]@row) - 1; MONTH([Fin contrat]@row) + 8; DAY([Fin contrat]@row) - 15))
=IFERROR(IF([Fin contrat]@row >= TODAY(); WORKDAY([Fin contrat]@row; -120); ""))
Can you help me.
I apologise for the mistakes. I don't speak english well.
Thanks,
Sonia
Answers
-
Try,Β
=[Fin contrat]@row - 167)
Below is how I arrived at 167 days for 5.5 months.
- 365 days in 1 year.
- 12 months in 1 year.
- (365/12) * 5.5 = 167
As to why your formula is failing...
- The expression
MONTH([Fin contrat]@row) - 5
will return values <1 when the month is January - May. - The expression
MONTH([Fin contrat]@row) + 8
will return values >12 when the month is May - December. - The expression
DAY([Fin contrat]@row) - 15
will return values <0 when the day of the month is less than the 16th.
These will three will cause the function DATE() to fail. In addition, you are using incorrect syntax for the functions. You can find documentation on Smartsheet functions here, https://help.smartsheet.com/functions.
Good luck!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 371 Global Discussions
- 205 Industry Talk
- 436 Announcements
- 4.5K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 448 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!