cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## USING MOD Function on Dates

Atom

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?

Highlighted
Alteryx Certified Partner

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"

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,

Magnetar

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')`