754622_en-US

liliconcom

sum date + month

Hello everyone,

I would like to make a sum between 2 columns (date and month).

Here is an example below:

How could I create a new column which would result in the sum of the two columns and give: "02/09/2024".

The date column is in date d/m/y format and the time column is in “month” format with format customization.

Thank you for your reply.

Data AccessRe: sum date + month

The formula is

If( Month( :date ) + Num( :"time (month)"n ) <= 12,<BR />Date MDY( Month( :date ) + Num( :"time (month)"n ), Day( :date ), Year( :date ) ),<BR />Date MDY( (Month( :date ) + Num( :"time (month)"n )) - 12, Day( :date ), Year( :date ) + 1 )<BR />)

 

Re: sum date + month

There is no "month" function, likely because months have varying lengths. So I took a brute force approach to create a formula, but I believe it will work. Look at the New Date column in this file.

 

Re: sum date + month

You can try using Date Increment() , but you might have to modify your month value (it seems to be character in your case, so use Num() to convert it in a formula). 

 

Names Default To Here(1);

dt = New Table("TEST (4)",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("time", Numeric, Continuous, Set Values([4]), ),
	New Column("date", Numeric, "Continuous", Format("d/m/y", 12), Input Format("d/m/y"), Set Values([3797452800]), )
);

dt << New Column("a", numeric, continuous, Format("d/m/y", 12), Formula(Date Increment(:date, "month", :time, "actual")));

It might also be important to check how your solution works in possible corner cases (Date Increment(31Jan2024, "month", 1, "actual"))

 

Re: sum date + month

Good morning,

Your answer works for adding the months. But when the sum of the date + month exceeds the current year, we get a problem (no result or the year does not become year +1):

Here, the result of the "sum" column should be: 02/05/2025

How can we get around this problem?

Thank you for your reply.

Re: sum date + month

Did you try with the solution I provided?

Re: sum date + month

yes it works correctly !