Dates Formula

[angel0v89]

Hi guys, I was struggling to find a better solution for my "not very clever" formula. 

In the formula tool I am currently using: 

if Between('2024-07-31', [Start_Date], [End_Date]) then '2024-07-01'
elseif Between('2024-08-31', [Start_Date], [End_Date]) then '2024-08-01'
elseif Between('2024-09-30', [Start_Date], [End_Date]) then '2024-09-01'

elseif Between('2024-10-31', [Start_Date], [End_Date]) then '2024-10-01'

....

etc etc. until the Max of End Date (currently 2033) 

Is there any smarter way to avoid repeating this dozens of times? 

Many thanks,

Alex

[Deano478]

@angel0v89 can you share some sample data so we can test on it?

[angel0v89]

Start Date	End Date	Amount
2024-07-14	2027-05-01	50
2026-02-16	2028-02-01	100
2024-08-15	2025-03-02	60

[craigja]

what is it you are trying to do? Are you looking to get the 1st day of the month given a date during the month? If so I think this will do it:

DateTimeParse(DateTimeFormat([DATE FIELD], "%Y-%m") + "-01", "%Y-%m-%d")

[angel0v89]

I have 2 dates, start and end date. So I need to create an additional "Date" column which contains every single month in a format of YYYY-MM-01.

In each month "Date" column , I need to look if the the end of the calendar month is between Start and End Date to see if this this month is in the bracket of Start and End Date. So for the above example this will be the final output

Date	Start Date	End Date	Amount
2024-07-01	2024-07-14	2027-05-01	50
2024-08-01 etc.etc.	2024-07-14 etc.etc	2027-05-01 etc.etc	50 etc.etc.
2026-02-01	2026-02-16	2028-02-01	100
2026-03-01 etc.etc	2026-02-16 etc.etc	2028-02-01 etc.etc	100 etc.etc
2024-08-01	2024-08-15	2025-03-02	60

[craigja]

Still not totally sure what you are doing but you can use the Generate Rows tool to create rows with the first day of each month between 2 dates.  See the screenshot - your initial expression is the date you want to start from, the Condition is the date you want to end on and the loop uses the DateTimeAdd to add 1 month to each row

[TUSHAR050392]

Hey @angel0v89 

Are you trying to get the Date column using Start Date? If so, then you can use the formula ToDate(DateTimeTrim(Start Date, "firstofmonth") - This will give you the Date column. 

If you then want to compare if the Start date falls between Date and End date then simply create a new column and use formula Start date > Date and Start Date < End date.

Data type should be bool and it will give true & false if a data is in between or not. Make sure all data types are date when comparing. Hope this is what you are looking for.