# Excel Equivalent Formulas

Some Excel functions can be replicated in Anaplan using Formulas.

 Excel Function Anaplan Formula YEARFRAC tested to 3 decimal places Create three line items Two date-formatted line items for the two dates End Date & Start Date One number-formatted line item with the formula To replicate US (NASD) 30/360 (Basis 0) `(YEAR(End Date) - YEAR(Start Date) + 30 * (MONTH(End Date) - MONTH(Start Date)) + IF DAY(Start Date) > 29 AND DAY(End Date) > 30 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 AND DAY(End Date) = 28 THEN 30 ELSE DAY(End Date) - IF DAY(Start Date) = 31 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 THEN 30 ELSE DAY(Start Date)) / 360` To replicate Actual Days/Actual of days in year (Basis 1) `No of Days * 360 / 365 / 360 (Accurate to 2 decimal places)` To replicate Actual Days/360 days in year (Basis 2)`(End Date - Start Date) / 360` To replicate Actual Days/365 in year (Basis 3) `(End Date - Start Date) / 365` To replicate European 30/360 (Basis 4) `((MONTH(End Date) - MONTH(Start Date) - 1) * 30 + 30 - (IF DAY(Start Date) < 30 THEN DAY(Start Date) ELSE 30) + IF DAY(End Date) < 30 THEN DAY(End Date) ELSE 30 + (YEAR(End Date) - YEAR(Start Date)) * 12 * 30) / 360` NETWORKDAYS Create four line items Two date-formatted line items for the two dates -End Date & Start Date; One number-formatted line item for an integer representing the total days holiday - Holidays Create fourth number-formatted line item with the formula.`End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - MOD(End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0, 7)) / 7 * 2 - Holidays` MMULT Create three lists for the matrix dimensions: I, J, K. Add a parent item in list K to sum intermediate products. Create two modules Matrix A and Matrix B to use as the input matrices. Configure Matrix A with one line item (Value) set as Pages and the lists I set as Rows and & K as Columns. Configure Matrix B with one line item (Value) set as Pages and the lists K set as Rows and & J as Columns—the column count of Matrix B must match the row count of Matrix A. Create a module Matrix C as the output matrix. Configure Matrix C with one line item (Output) as Pages and the list I as Row, and lists J & K as Columns. In the module, hide every occurrence of the K dimension, except the parent (Sum) column. The formula for the line item (Output) in Matrix C is `Matrix A.Value * Matrix B.Value`