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?

Help

Accepted answers

dwstada

@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

All comments

dwstada

@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

Screenshot 2024-08-05 062228.png

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703