,How to skip counting weekends between two dates

Hi,

I need to count 5 days from Date1 which shouldnt be including saturday and sunday. For example

My Date1 is : 25th Jan 2024 (thursday)
Output I need : 1st Feb 2024 (thursday) skipping 27 and 28th of Jan because those two fall on weekend.

How to incorporate this in formula tool?

I have used this formula:
IIF(DateTimeformat(DateTimeAdd([Questionnaire Received],5,'days'),'%A') == 'Sunday',DateTimeAdd([Questionnaire Received],6,'days'),IIF(DateTimeformat(DateTimeAdd([Questionnaire Received],5,'days'),'%A') == 'Saturday',DateTimeAdd([Questionnaire Received],7,'days'),DateTimeAdd([Questionnaire Received],5,'days')))

This is working fine only when my due date falls on saturday and sunday it will add extra days but what i need alteryx to do is to skip counting weekends itself!

Appreciate any help. Thank you in advance

Workflow

Designer Cloud

Date Time

Accepted answers

danilang

Hi @Sanjana_HS

Try this

If DateTimeDay([StartDate]) = 6 then
	DatetimeAdd([StartDate],9,"days")
elseif DateTimeDay([StartDate]) = 7 then
	DatetimeAdd([StartDate],8,"days")
else
	DatetimeAdd([StartDate],7,"days")
endif

If your start date is Saturday add 9 days, if Sunday add 8 days, else add 7.

Dan

binu_acs

@Sanjana_HS

All comments

aatalai

use the TS filler tooler, and set it to only weekdays, and then count record tool to see how many are inbetween the days

Sanjana_HS

I dont have that tool. Can you suggest something else

aatalai

@Sanjana_HS can you download it/install it?

https://help.alteryx.com/current/en/designer/tools/time-series/ts-filler-tool.html#ts-filler-tool

aatalai

otherwise let me know and I can suggest a way using generate rows, but it will be significantly more complicated

aatalai

@Sanjana_HS how are you getting on?

danilang

Hi @Sanjana_HS

Try this

If DateTimeDay([StartDate]) = 6 then
	DatetimeAdd([StartDate],9,"days")
elseif DateTimeDay([StartDate]) = 7 then
	DatetimeAdd([StartDate],8,"days")
else
	DatetimeAdd([StartDate],7,"days")
endif

If your start date is Saturday add 9 days, if Sunday add 8 days, else add 7.

Dan

binu_acs

@Sanjana_HS

Sanjana_HS

It helped. But what if i want to add 10 days for the same date. Will it work the same? Because what i understood was it will work only for 7 days for a week

For Example:

From the above formula i got date as 26th Jan 2024. Now from this again i need to add 10 days with same logic skipping weekends.

Akash08

Hi,

1) First find the initial day of the week on initial date.

2) Then do T+5 and find its T+5 day as well

3) Check if T+5 day is Sun, Sat, Mon, Tue then add 2 more days

find the workflow attached. Hit as a solution if this helps. Thank you.

Working T+5.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703