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

[sylvia726]

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 Date	Usage End date	Amount	Output
01-06-20	20-06-20	3,000	PART
01-06-20	30-06-20	2,000	FULL
01-06-20	30-06-20	5,000	FULL
01-06-20	30-06-20	6,000	FULL
05-06-20	30-06-20	2,500	PART
10-06-20	20-06-20	1,500	PART

[ChrisTX · Accepted solution]

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

[sylvia726]

Many thanks for your prompt response. It works.