How to get custom date count while excluding holidays & weekends?

[TRIPAVI6]

Hi All,

Need one help with the calculation. I need to get day count from the latest date in data. This latest date will be dynamic and keep updating on daily basis as I get new data everyday.

I need to calculate days passed using this dynamic latest date while excluding weekends, & if any date missing b/w. For weekends, day count of last weekday comes.

For Ex:

25/11/20 - Day1

24/11/20 - Day2

23/11/20 - Day3 

22/11/20 - Day3 (weekend)

21/11/20 - Day3 (weekend)

20/11/20 - Day4

18/11/20 - Day6 ( As 19/11/20 is missing hence, Day 6)

17/11/20 - Day7

Can anyone help with the solution for the same?
Thanks,

[TRIPAVI6]

You almost saved me but check this below snapshot. 

Tuesday - 56

Sunday - 56 (fine)

Saturday - 57 ( should be 56 too)

Also, Is there any why to include holidays in this calculation? I mean likewise same Day # continue if there is any holiday. 

[atcodedog05]

Hi @TRIPAVI6 

Fixed and added an option specify holidays also

Give the holiday list in the highlighted place

[TRIPAVI6]

It's still kind of unresolved. Please check the below. I know it's soo typical & I'm also struggling since couple of days. 

Sunday should be Day 55 as last Tuesday but process is adding up & then retaining.

Please help & thanks for the holidays input.

[atcodedog05]

Hi @TRIPAVI6 

Even though a weekday is missing it adds a day right.

Monday is missing it added a day.

[TRIPAVI6]

Not really!
It should add a day only based on days/date coming from data. If any weekday is missing in data, then next day will get +1 in day increment, not +2.

[atcodedog05]

@TRIPAVI6 

Hmm that adds a different logic. Let me give it a try.

[TRIPAVI6]

Have figured it out brother. Thanks for helping me out though.

[atcodedog05]

Thats great to hear 😀

Can you share your solution. If you dont mind. It will great to know how you solved it. 🙂

[TRIPAVI6]

Actually it's pretty simple. I wonder why it didn't strike before.

I've taken unique at ID & Date, then flagged date for working day, then took running total based on working day flag.

As for holidays & weekends, flag =0 hence, running total will add 0 into previous total i.e. basically same days count on weekends.

Also, working day flag is based on dates in data, hence, the day count will also come based on available dates.

Thanks,