Forum Discussion
Solved
Invalid date error: Resulting value falls outside the range of allowed values
Hello all, I have an error relating to dates, which arises from an error in data provided by the user. The user had input 29 February for years which are not leap years. Is there a way to fix th...
On the basis of sample provided by you - Use this formula in a custom column
= try Date.From([Date]) otherwise Date.From(Text.Combine(List.ReplaceValue(Text.Split([Date],"/"),"29","28",Replacer.ReplaceValue),"/"))
As the data is confidential, I would prefer not to. It is basically a table with different columns, one of which is a date column containing different dates.
| Col1 | Col2 | Date | Col4 |
| a | d | 1 August 2025 | x |
| b | e | 29/2/2022 | y |
| c | f | 1 January 2030 | z |
| ... | ... | ... | ... |
Some of the dates are incorrect and invalid, showing a leap day in a non-leap year, e.g. 29 February 2022. I want to make these dates 28 February 20xx (where xx is the relevant year, and in this example it is 2022), while keeping the dates for the other records the same.
On the basis of sample provided by you - Use this formula in a custom column
= try Date.From([Date]) otherwise Date.From(Text.Combine(List.ReplaceValue(Text.Split([Date],"/"),"29","28",Replacer.ReplaceValue),"/"))