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 m_dekorte . LD_CourseExpiry_Odata.Days Until Expiry is calculated at the source system. In this dataset, I have the Effective End date of the course and Days Until Expiry, but not the Course Completion Date (in some cases), hence the need to calculate it. So I then inverted the Days Until Expiry figure by multiplying by -1 to get the negative value. I then did an Add.Days formula to find the original course completion date. I have had to do a lot of work to get the dates in the right format before doing the calculation, as they feed down incorrectly from the source system (eg 3/9/2024 instead of 03/09/2024) and I don't know if this has something to do with it, as my code seems to be ok. Thanks for your input.
To me, it seems that the original course completion date can’t be derived this way. You could look for other fields in your dataset or bring in additional ones that provide the information you need.
As for the date format issue (e.g., 3/9/2024 instead of 03/09/2024), you can use the optional third parameter, Culture, in your Changed Type step. This lets the M engine know which format to expect, for example, specifying "en-US" for U.S. date formats.
Table.TransformColumnTypes(
PreviousStepName,
{{"Effective_End", type date}},
"en-US"
)Illustrated here.