monday Formulas - Part 4: The SECRET Functions скачать в хорошем качестве

monday Formulas - Part 4: The SECRET Functions

This is part 4 of our series about monday.com formulas. In this video I will reveal some of the hidden/secret functions available to use in the formula column. Link to Hidden Functions Documentation: https://themondayman.com/hidden/ BACKWARD WORKDAYS FORMULA: (Calculates a new date subtracting WORKDAYS from a given date accounting for holidays.) To use it... set up columns {Date}(date or formula), {Days}(number or formula), {Holidays}(formula}, and another formula column to contain this formula. If you want to have different names, set it up this way then after everything is working, change the column names. This formula will automatically change. put your holidays in the {Holidays} column in a string with dates in the format YYYY/MM/DD separated by commas, e.g.: "2021/12/24,2021/12/31" (no spaces). copy and paste this formula text into your formula column. change your column headings (if you want). IF(AND(GTE({Days},0),INT({Days})={Days},{Date}) ,FORMAT_DATE( SWITCH({Days}, 0, {Date}, INT(NETWORKDAYS(FORMAT_DATE({Date},"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), {Date}, INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7-2)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7-2)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7-1)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7-1)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7-0)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7-0)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+1)), "Y/M/D"), FORMAT_DATE({Date}, "Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7+1)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+2)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+2)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+3)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+3)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+4)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+4)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+5)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+5)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+6)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+6)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+7)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+7)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+8)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+8)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+9)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+9)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+10)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+10)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+11)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+11)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+12)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+12)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+13)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+13)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+14)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+14)), INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+15)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+15)) , "ERROR"), "MMM D, YYYY"), "") ⏱Outline/Time Stamps 00:00 Intro 01:54 Logical: NOT, LT, EQ, GTE, LTE, CHOOSE 04:25 Numeric Functions: INT, SIGN, PROODUCT, ODD, EVEN 07:07 Date Functions: WEEKDAY, YEARFRAC, EDATE, EOMONTH + WORKDAY, NETWORKDAYS 10:04 Array Functions: ARGS2ARRAY, SPLIT 13:44 String Functions: ROMAN, PROPER, FIND, REGEXEXTRACT, (REGEXMATCH, REGEXREPLACE) 17:10 Regular Expressions and Subtract Workdays formula Examples Monday Man Links Create Make account: https://www.make.com/en/register?pc=t... Contact The Monday Man: https://themondayman.com/contact/ Hire Me: https://themondayman.com/custom/ #monday #formulas #hidden #secret