Forum Discussion
Date.AddDays not working with negative numbers
re:"You can see from the following table that when I use positive numbers I get the correct output, and when I use negative I just get today's date (CourseExpiry_Completion_Date1 and CourseExpiry_Completion_Date2):"
When I look at some of your dates in your screen shot:
and I put these data into a plain sheet and do the subtraction there I get:
That means the subtractions are correct.
So have a look at how the values in column ~.DaysUntilExpiry are calculated!
Hi Kerria1276,
You don't need anything special for negatives - Date.AddDays accepts negative offsets. The usual culprit is type coercion: in M, as number is a type assertion (not a cast). If your column is text like "-30", as number won’t convert it; use Number.From instead. Also make sure the first argument is a date (not text).
Simple fix
= Table.AddColumn(
#"Changed Type3",
"CourseExpiry_Completion_Date",
each Date.AddDays(
Date.From([Effective_End]),
Number.From([DaysUntilExpiry_Negative])
),
type date
)
Defensive version (handles nulls/bad text and the Unicode minus)
= Table.AddColumn(
#"Changed Type3",
"CourseExpiry_Completion_Date",
each
let
baseDate = try Date.From([Effective_End]) otherwise null,
rawText = try Text.From([DaysUntilExpiry_Negative]) otherwise null,
normalized = if rawText <> null then Text.Replace(rawText, "−", "-") else null,
offset = try Number.From(normalized) otherwise null
in
if baseDate <> null and offset <> null
then Date.AddDays(baseDate, offset)
else null,
type date
)
References:
- Date.AddDays docs
- Number.From docs
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.