Workflow returning incorrect values when dealing with dates.

[WishIKnewHowToCode]

Hey,

I can't share my specific workflow because it has patient data, but I have a list of Medicare Dates, there are many of these. And then I have Billing Dates. I need to find a Medicare Date that is within a range of -7 to 120 days of the Billing Date.

I tried using the following custom formula in a filter tool.

[Medicare RA Date] >= DateTimeAdd([Date First Bill Sent to Beneficiary], -7, 'days') AND
[Medicare RA Date] <= DateTimeAdd([Date First Bill Sent to Beneficiary], 120, 'days')

But as a result I'm getting dates that are not within -7 days and 120 days.

For example: My Date First Bill Sent to Beneficiary is 11/28/2020 but the result for Medicare RA Date I'm getting is 2/26/2021, which is -90 days from the Billing Date.

[Qiu]

@WishIKnewHowToCode 
First please make sure that the coumns [Medicare RA Date] and [Date First Bill Sent to Beneficiary] are in Date format.

And it appears to me that 2/26/2021 is correct result?

[AGilbert]

I believe that date is +90 days. It should fit your filter expression. 

[WishIKnewHowToCode]

It is within 90 days, but on the wrong end of 90 days. Which is kind of where I knew I had a mistake, but I'm not sure what's wrong. @Qiu @AGilbert 

[AGilbert]

I believe that Excel (and many computer systems) considers date data type as the number of seconds elapsed from when they started counting, the epoch. Therefore, subtracting a later date from an earlier date returns a negative number; because you're subtracting a larger number of seconds from a smaller number of seconds. 

[WishIKnewHowToCode]

So I've used this workflow for a similar situation but not this exact range of time. I've tried defining the parameters with a formula, I've created additional fields using -7 and +120 on the target date to create the "range" manually and tried using that, but I keep getting -90. And it matters because this is a regulatory thing, that's the formula for determining if the date is valid, and I'm trying to find substitute dates. 

[AGilbert]

If you changed the Excel formula to B3-A3 would the result be 90? That seems to be your desired output. 

[WishIKnewHowToCode]

Yes, but that's not how it works. I can't just change the excel formula, because that's the regulation. Regulation is Date of First Bill must be within -7 to 120 Days of the Medicare RA. The Date of First Bill cannot be before the Medicare RA.

Just to explain this a bit, the patient can't be billed until the facility has billed insurance (Medicare). So the Date of First Patient Bill should never come before the Medicare RA since Medicare is the primary insurer in this case. (it does happen and that's a billing error, but it is not overwhelmingly common). So if I submitted this for an audit, it would get disallowed because the auditor would immediately notice the RA is after the First Bill Date which is incorrect.

[WishIKnewHowToCode]

If I changed the Formula in Alteryx and flipped the - signs so it's 7 and -120, would that give me the result I want?

[AGilbert]

It's hard to know what result you want without seeing the workflow. You can either replace patient data with something else or you can mock-up an example of the relevant parts.

Regarding the filter formula. If you changed it to this, you'd get the opposite result of your original expression. I'd suggest changing the conditional/boolean expressions over swapping the 7 and -120. It's easier to maintain/explain.

[Medicare RA Date] < DateTimeAdd([Date First Bill Sent to Beneficiary], -7, 'days') OR 

[Medicare RA Date] > DateTimeAdd([Date First Bill Sent to Beneficiary], 120, 'days')