Excel Function Workday and Networkdays in Alteryx

Hi Community!

I am automating a large repeatable report that is hundreds of thousands of rows.

I have searched and looked at several workflow solutions proposed for Workday and Networkdays.. but my date in Workday can be any working day and my start date in Networkdays can also be any day of the week. I haven't seen any solutions that will accommodate my scenario.. If there is one out there that I have missed please share it with me 🙂

Steps to bring out the AP Date is put the formula =WORKDAY(Vendor Paid Date,-2, Holidays)

Steps to bring out the “Days 2 Pay Put the formula =Networkdays(Accounting date, AP Date, Holiday Table)-1

Any help is greatly appreciated!!

Grace

Date Time

Accepted answers

mst3k

you could use the generate rows tool to produce a row for each day between START and END which is a straightforward approach that could work perfectly for you. but in the event you have a significant amount of data and/or years between the dates, that could be very inefficient and generate many many times the size of your original dataset in processing. if your dates are 1 year apart, you'll have 365 rows for each row you started with, if you have 1 million records you'll make poor old alteryx crunch 365 million rows

if that's a concern at all, then i have a more efficient approach for that scenario. i started by calculating how many full weeks are between the dates, and we know there should be 5 weekdays in each of those, plus adjusting for if the start/end is on a weekend. then for any remaining partial week to get to the end date, we can generate rows just for those days (which will never be more than 7 per original record, so in the above example alteryx would only have to crunch 7 million records instead of 365 million). then throw out the saturday/sunday in that partial week.

finally, merge in an optional list of holidays, check if they're between the start/end, and adjust for those

NETWORKDAYS.yxmd

mst3k

here, i packaged it into a macro. so you can pick which date fields you want to compare, and reuse it as many times as you need to calculate between other dates

the holiday connector is optional. if you use it, you'll have to pick the field map on the macro to select the field name to map into it. if you don't use the H input, you don't have to map anything in that dropdown in the macro even though it appears red. this should run fine as is - don't be surprised in the event something shows a ! error before you run it, i just threw this together and sometimes macros think funny when they haven't been run yet.

NETWORKDAYS example.yxzp

All comments

apathetichell

O.k. - you probably want to post some data including a holiday file so I can mock up a workflow... but basically:

in alteryx you'd probably do this a bit differently. I'd start by having a list of holidays in date format (just like you need in excel)... Then for your networkday function you create a range of dates using generate rows for the days between accounting date and ap date. You then use a join filter out the holidays (ie the matched entries). You then use summarize to take a count. Then you subtract 1. Voila.

you'll probably use something similar where you create a range - from min to max. use a join to dump the holidays. and then your workday is the date in your row-2 in a multi-row formula.

RolandSchubert

Hi @Gr4c3Sult ,

an approach for your problem is to generate a list of dates, starting with your start_date and adding/subtracting the number of days (second param in function). The next step is to remove weekend days (using the DateTimeFormat function you can identify the weekday and use a Filter tool to select only working days.

To also remove holidays, you have to provide a list (like the range in Excel), You can e.g. use a Join tool and keep only the dates from your generated list, that are not in the holiday list ("L" output).

WORKDAY return the last workday date in the range, you can use a Summarize tool using "MAX" to find it, NETWORKDAYS return the number of days, a Count tool will help.

Please let me know, if this meets your requirement?

Best,

Roland

Workday_Networkdays.yxmd

mst3k

finally, merge in an optional list of holidays, check if they're between the start/end, and adjust for those

NETWORKDAYS.yxmd

Gr4c3Sult

@apathetichell ,

Thank you for the suggestion. I have attached an example of the dates and holidays. I have played with multiple ways, and I am failing 😞 your help is appreciated.

PWP SLA Report - Aug 2021 cleaned.xlsx

Gr4c3Sult

@RolandSchubert

I don't think that will work.. I have attached a sample of my data and expected results. Each row has different dates starting on different days of the week. How does alteryx know to assign 6 and 7 to Saturday and Sunday?

Appreciate your help!!

PWP SLA Report - Aug 2021 cleaned.xlsx

apathetichell

@mst3k- you can avoid the 365 million row conundrum by using a macro.

@Gr4c3SultAlteryx can extract saturday or sunday (or 6/7) from a date using datetimeformat([date],"%u") provides 6 or 7 for Saturday or Sunday. - if you're going to have 1,000,000 or so rows I'd build as a macro since it makes more sense.

mst3k

the macro will still have to generate and crunch 365 million rows in that scenario. a straight generate rows tool as a solution works fine, but it will end up generating a row for every day between the dates of every record in your data, so if you have 1 million rows a year apart, alteryx will have to generate 365 million rows whether it's in 1 fell swoop or chunked out into macro runs unfortunately. you won't have 365 million rows at the end - but alteryx will still have produced them during the run and it could potentially cause long run times and heavy processing usage

mst3k

here, i packaged it into a macro. so you can pick which date fields you want to compare, and reuse it as many times as you need to calculate between other dates

NETWORKDAYS example.yxzp

mst3k

oh and definitely sample it and test a few out in excel just to make sure it's getting the right answers... i did that myself but you should probably check too

Gr4c3Sult

@mst3k

Thank you for all your help!! I am wondering if this Networkdays solution can be modified to solve =WORKDAY(Payment Date,-2, Holidays)?

What you did in the Networking day workflow is beyond my skill set. I have so much to learn!!

Thanks again for your time 🙂

Grace

Vinayakk88

Master solution using one single formula tool!!!

// Calculate total days (inclusive of start and end date)
TotalDays = DateTimeDiff([EndDate], [StartDate], "days") + 1

// Calculate number of full weeks
FullWeeks = Floor([TotalDays] / 7)

// Calculate remaining days beyond full weeks
RemainingDays = Mod([TotalDays], 7)

// Get start and end day of the week (0 = Sunday, 6 = Saturday)
StartDay = DateTimeFormat([StartDate], "%w")
EndDay = DateTimeFormat([EndDate], "%w")

// Initialize weekend adjustment
WeekendAdjustment = 0;

//Add following formula in [WeekendAdjustment] Field
// Adjust for extra weekend days in the remaining days beyond full weeks
IF [RemainingDays] > 0 THEN
   // If start is after Saturday, count weekend days within the remainder
   IF ([StartDay] + [RemainingDays]) =7 THEN 1
   ELSEIF ([StartDay] + [RemainingDays]) > 6 THEN  2 
   ELSEIF ([StartDay] = 6) THEN 1
   ELSEIF ([StartDay] = 0) THEN 1
   ENDIF
ENDIF

// Final Network Days Calculation
NetworkDays = [TotalDays] - ([FullWeeks] * 2) - [WeekendAdjustment]

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703