Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate Net hours excluding weekends

sriku
7 - Meteor

Hi All,

 

I need to calculate net hours from the start date and difference it from extract date and time

 

Exclusion of weekend from start date :

Sat :6:30:00 AM

Mon : 2:30:00AM

 

 

 

Regards,

Srikant

6 REPLIES 6
ChrisTX
15 - Aurora

If you search the community for work hours or weekends, you should find several examples

 

Here's one: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Working-Hours-calculation-excluding-we...

 

DavidP
17 - Castor
17 - Castor

Can you check if the following gives you the right answer

 

DavidP_1-1582558636882.png

 

DavidP
17 - Castor
17 - Castor

I just realised that I included 2 attachments in my post. The 1st one is incorrect as it assumes 48 hours for a weekend (midnight to midnight), 

 

The 2nd attachment uses 44 hours for a weekend, Sat 06:30 to Monday 02:30.

 

There is an assumption that the start date and extract date don't themselves fall on weekends.

sriku
7 - Meteor

Hi David,

 

Thanks for your prompt response.

 

Hope you have excluded 44 hours from the start date and start time and deducted it from extract date and time.

 

Shall I considered the 2nd solution

 

Please let me know if require further details

 

Regards,

Srikant

DavidP
17 - Castor
17 - Castor

Yes, look at the 2nd solution.

 

It basically calculates the difference in hours between the extract date time and the start date time and then subtracts the weekend hours from this number.

 

So if start date is in week 5 and extract date is in week 7, the weekend hours are 2*44.

 

This won’t work if start date and extract date are in different years. In that case we’ll have to adjust the logic.

DavidP
17 - Castor
17 - Castor

I noticed this morning that the formula needs to be able to deal with start and extract dates in different years.

 

Please see new version attached. This now correctly calculates the difference in weeks between the 2 dates even if they are in different years.

Labels