USING MOD Function on Dates

[Katarina32]

Has anyone been using the MOD function on a date field? I have a formula in EXCEL that is as follows

(DATE)-MOD(DATE-8,14)+13

which gives me the a bi-weekly end date for a given date.

When I try to use this formula in Alteryx I get an error and it seems like MOD will not work on a date in Alteryx.

Has anyone had any success finding a work around for using MOD on a date field?

[Thableaus · Accepted solution]

Hi @Katarina32 

MOD only works for numbers in Alteryx, as far as I know.

To replicate the conversion that Excel does, you need to compare your Date to "January 1, 1900"

See this article about how Excel does what it does: https://support.office.com/en-us/article/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252

There's a workaround that I did to get the final result. See Workflow appended.

EDIT: I forgot to subtract 1 in the final formula. Now the actual result. 

Cheers,

[Claje]

So the main reason why you're encountering issues has to do with how Alteryx stores dates compared to Excel.  In Excel, dates are stored as a number (with 1/1/1900 mostly being day 1), and Excel translating this number into a human readable date.

Alteryx treats dates as a distinct data type, and has specific functions which are used to interact with this data type.  The key advantage of this is that Alteryx can handle a much wider range of dates (try entering 1/1/1700 into excel, or doing date calculations with it).

To exactly replicate your current design, I would use the following formula for your MOD function:

MOD(DATETIMEDIFF([date],'1900-01-01','days')+2-8,14)

Datetimediff() will return a number, in this instance the number of days since 1/1/1900, which is the starting point for all Excel calculations.  We then Add two, because this calculation would otherwise start at 1/1/1900 as 0 instead of 1, and would only count the difference in days, so it would exclude the current date.

To create your entire formula, I would recommend the following:

DATETIMEADD([Date],-(MOD(datetimediff([Date],'1900-01-01','days')+2-8,14))+13,'days')

Here's what we added:

DateTimeAdd() is the function that lets you do math with date fields in Alteryx.  We added a "-" sign to our MOD value so that it is treated as a negative value, and then added 13.

This should be consistent with your Excel formula.