Problem with IF statement and date

[Julie_Clarke]

Hi,

I'm new to Alteryx and am trying to convert a formula I have working in Excel for use in Alteryx:

IF([Start Date]> DateTimeToday(),
[Finish Date]-[Start Date],
IF([Finish Date]-DateTimeToday()<=0,7,[Finish Date]-DateTimeToday()))/7

But I'm getting an error of Error: Formula (21): Parse Error at char(32): Malformed If Statement (Expression #1)

My original code was:  

=IF(Start Date>TODAY(),Finish Date-Start Date,IF(Finish Date-TODAY()<=0,7,Finish Date-TODAY()))/7

Any suggestions or solutions would be much appreciated.

[ShankerV]

Hi @Julie_Clarke 

Try this

IF [Start Date]> DateTimeToday()

THEN [Finish Date]-[Start Date]

ELSEIF [Finish Date]-DateTimeToday()<=0

THEN 7

ELSE [Finish Date]-DateTimeToday()/7

ENDIF

[binuacs · Accepted solution]

@Julie_Clarke Use DateTimeDiff function for taking the date difference

IIF([Start Date] > DateTimeToday(),DateTimeDiff([Finish Date],[Start Date],'day'),
IIF(DateTimeDiff([Finish Date],DateTimeToday(),'day')<=0,7,DateTimeDiff([Finish Date],DateTimeToday(),'day')))/7

[Julie_Clarke]

Of course - thank you.

[Julie_Clarke]

Hi 

Thanks for this, but this generates an error 

Error: Formula (21): Parse Error at char(147): Formula: tried to apply numeric operator to string value (Expression #1) ??

My dates are in date format.

[binuacs]

@Julie_Clarke It is difficult to say what might be the issue without seeing your input data. I am attaching a sample workflow that uses the same formula on two date fields. Can you compare with your workflow?

[Julie_Clarke]

Hi,

Thanks for your interest / help.

Attached is some sample data with the core date fields.  As you'll see from this ,my duration calculation (Column p) gives me decimals rather than integers.

Using the calculation suggested I only get full numbers - I'm obviously missing something ??🙄

[binuacs]

@Julie_Clarke Changing the data type in the formula tool to an integer will solve the decimal issue

[Julie_Clarke]

Hi, 

I did that initially and it didn't seem to work, hence my email but have now tried again with success - thank you for confirming the approach.