Problem with IF statement and date

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.

Workflow

Tips and Tricks

Date Time

Transformation

Accepted answers

binu_acs

@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

All comments

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

binu_acs

@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

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.

binu_acs

@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?

DateDiff_080223.yxmd

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 ??🙄

Sample Data.xlsx

binu_acs

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

DateDiff_090223.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703