Is it able to sort out the full Calendar Month from "usage start date" & "usage end date"?

Question

Hi all,

I have two column of dates i.e. usage state date and usage end date with billing amount.

Is Alteryx able to identify (like column "Output") if the billing amount is for full calendar month or partial month?

For Example

Usage Start DateUsage End dateAmountOutput01-06-2020-06-20                 3,000PART01-06-2030-06-20                 2,000FULL01-06-2030-06-20                 5,000FULL01-06-2030-06-20                 6,000FULL05-06-2030-06-20                 2,500PART10-06-2020-06-20                  1,500PART

ChrisTX · Accepted Answer

The little used "datetimetrim" function will trim dates to either 'firstofmonth' or 'lastofmonth'

If your date fields are type String, use two DateTime tools to convert the data type to Date

This formula should work:

IF ToDate(DateTimeTrim([Usage Start Date_Out],'lastofmonth') ) = [Usage End Date_Out] 
AND 
ToDate(DateTimeTrim([Usage Start Date_Out],'firstofmonth') ) = [Usage Start Date_Out] 
THEN 'FULL'
ELSE 'PART'
ENDIF

Chris