community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

USING MOD Function on Dates

Meteoroid

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?

Alteryx Certified Partner

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. 

 

DateConversionExcel.PNG

 

Cheers,

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

 

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.

Labels