Home → Troubleshooting → @RISK for Excel: Simulation → Incorrect Percentiles for Date Distributions
Applies to:
@RISK models with or without MPP files
I'm simulating with a project. Cell F2 contains a distribution of dates. In another cell, I have a function, RiskTarget(F2, 8/12/2017) or RiskXtoP(F2, 8/12/2017). Although the graph of F2 shows that the distribution extends before and after 2017, @RISK shows 0 for the RiskTarget or RiskXtoP function.
This is actually nothing to do with using projects, or even with @RISK; it's purely an Excel issue. When you write 8/12/2017 inside a function, Excel treats that as a division, and computes a double-precision value of about 0.00033.
That number doesn't look like a date, but it is. Excel dates are actually numbers, the number of days past 1 January 1900. A whole number means midnight at the start of a date; a number with a decimal part means some time after midnight on that date. Your RiskTarget or RiskXtoP function is asking @RISK for the percentile of your distribution that corresponds to 0.00033, which about 29 seconds past midnight on 1 January 1900. Since the minimum of your distribution comes after that date, 0 is the correct answer: a date in 1900 is in the zeroth percentile of the distribution.
How can I use dates with RiskTarget or RiskXtoP?
When you need to pass a date as an argument to any function in Excel, use the DATE function. Again, this applies to any function in Excel, not just @RISK functions. DATE takes three arguments: year, month, and day—in that order, regardless of your date formats. 8/12/2017 might mean 8 December or 12 August, depending on where you are, but DATE(2017,12,8) always means 8 December.
Examples: RiskTarget(F2, DATE(2017,12,8)) and RiskPtoX(F2, DATE(2017,12,8)).
Like all Excel functions, DATE changes in non-English versions of Excel. For instance, DATE is FECHA en español, DATA em português. There are numerous Web sites that provide translations of Excel function names, such as Excel Function Name Translations and Microsoft Excel Function Translations (both non-Microsoft sites). If you need to translate function names often, you might want to install Microsoft's Excel Functions Translator add-in.
Last edited: 2018-06-13