Calculate remaining days in the month

Looking for ways to streamline a process to calculate the remaining days in the current month within Alteryx. I know how to do it mathematically, but with all the features in Alteryx, I am guessing their might be a more efficient way? Look for ideas or best practice.

Developer

Developer Tools

Reporting

Tips and Tricks

Date Time

Best Practices

Dynamic Processing

Accepted answers

JamesCharnley

@cstafford There's a function within the formula tool called DateTimeDiff which we can use here. You'll want your parameters to be another function called DateTimeLastOfMonth() and your date/today's date whichever you want, and your date part which is 'day'. It'd look like this:

difftomonthend.yxmd

Felipe_Ribeir0

Now looking at the solution that @JamesCharnley provided, i would replace this part of my solution DateTimeTrim(DateTimeToday(), 'lastofmonth') by DateTimeLastOfMonth().

It gives the same result (2023-01-31) but it is much more directly and clear.

ShankerV

Hi @cstafford

right(left(tostring(DateTimeLastOfMonth()),10),2)

The formula will work dynamically as DateTimeLastOfMonth() gives the last day of the month.

Using left and right function, the last day of the month is extracted for our solution.

So for this current month January 31 is displayed.

But for February 28 for normal year and 29 for leap year will be displayed.

Many thanks

Shanker V

All comments

Felipe_Ribeir0

Hi @cstafford

One way of doing this

Now looking at the solution that @JamesCharnley provided, i would replace this part of my solution DateTimeTrim(DateTimeToday(), 'lastofmonth') by DateTimeLastOfMonth().

It gives the same result (2023-01-31) but it is much more directly and clear.

ShankerV

Hi @cstafford

Please make use of the functions datetimetoday() and datetimelastof month().

Calculate remaining days in the month.yxmd

cstafford

Thanks for the solution. I knew there had to be much better way!

cstafford

Is their a way to calculate the number of days in the current month as well?

Jan = 31

Feb = 28

March = 31

etc.

ShankerV

Hi @cstafford

right(left(tostring(DateTimeLastOfMonth()),10),2)

The formula will work dynamically as DateTimeLastOfMonth() gives the last day of the month.

Using left and right function, the last day of the month is extracted for our solution.

So for this current month January 31 is displayed.

But for February 28 for normal year and 29 for leap year will be displayed.

Many thanks

Shanker V

cstafford

Thank you!

apathetichell

fyi - datetimelastofmonth() - only gives the last day of the current month.

DateTimeTrim([insert your date field here], 'lastofmonth') as referenced by @Felipe_Ribeir0 originally is more flexible since it will provide you the last day of any month based upon your date field or of the current month.

sshutchinson

Hello,

I have a question kinda similar to this example. In my data set I have data from Dec. 1-28th in column header "Date". Because there are 31 days in Dec., I need to estimate the rest of the data for the following dates: 29th, 30th, and 31st. To do so, I want to use a 7 day lookback. By that I mean, the 29th will use data from the 22nd. The 30th will use the data from the 23rd. The 31st will use data from the 24th.

Alteryx Question 2.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703