FORMULA TOOL - if function

[Basma_alrubaish]

Hi everyone ,

im trying to add an if function in formula for a semi annual report (jan-june, june-dec) of each year 

if [input] date is in (anyday-06-(year) then result in 01-01-(same year)

and if the [input] date is in (anyday-12-(year) then result in 01-06-(same year)

what should my formula look like?

[dwstada · Accepted solution]

@Basma_alrubaish your request is kinda confusing, but here is a formula for how I understood it

if DateTimeMonth([input]) <= 6
then "01-01-"+ToString(DateTimeYear([input]))
else "01-07-"+ToString(DateTimeYear([input]))
endif

this turns all dates from Jan-June into 01-01-2024 and July-Dec into 01-07-2024. Not sure why your semi annual report would use June twice, so I changed that to July.

Maybe this helps you already, otherwise please clarify what you want to do :)

[ChrisTX]

Try formula:

IF DateTimeMonth(DateTimeNow()) <= 6
THEN ToString(DateTimeYear(DateTimeNow()),0) + "-01-01"
ELSE ToString(DateTimeYear(DateTimeNow()),0) + "-06-01"
ENDIF

Output data type: Date

Check out the DateTime functions:   https://help.alteryx.com/current/en/designer/functions/datetime-functions.html

 

Chris

[Basma_alrubaish]

@ChrisTX thank you so much 

but this would be calculating based on todays value date right? i need something that takes the value date in my data like this for example 

input	output
12/31/2024	6/1/2024
6/30/2024	1/1/2024
12/31/2025	6/1/2025

[ChrisTX]

If the field [input] is data type Date or DateTime, just replace "DateTimeNow()" with "[Input]"    without the quotation marks