Pulling Working Days from a range

[danjackson5]

Hi All,

Quick query here. If I have Date A in A2 and Date B in B2, I'd like to extract a list of working days in column D.

Would be ideal if I can omit the weekends and pre-defined dates such as bank holidays (Which i can list)

Please see the small sample i have attached. Thank you for your help.

All the best
Dan

p.s. I'm just a about to finish for the day so if i don't get back to you straight away, no worries. I'll pick up in the morning. Cheers

[alansidman]

Since you are using O365, you have Power Query in your version. It is called Get and Transform Data and is found on the Data Tab. Here is the Mcode from using that process.

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C
1	Date From	Date To	Custom
2	7/19/2021	7/19/2021	7/19/2021
3	7/14/2021	7/16/2021	7/14/2021
4	7/14/2021	7/16/2021	7/15/2021
5	7/14/2021	7/16/2021	7/16/2021
6	7/22/2021	7/27/2021	7/22/2021
7	7/22/2021	7/27/2021	7/23/2021
8	7/22/2021	7/27/2021	7/26/2021
9	7/22/2021	7/27/2021	7/27/2021

Sheet: Table1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

This tutorial may help you to understand exactly how this worked.

https://www.youtube.com/watch?v=u3HLseZxSj4

[Bo_Ry]

With LET function

=LET(fr,A2:A4,to,B2:B4,nd,NETWORKDAYS(+fr,+to),sd,SUM(nd),sq,SEQUENCE(sd,,0),rw,ROW(fr),pr,PROB(rw,nd/sd,,rw-1)*sd,
WORKDAY(LOOKUP(sq,pr,fr),sq-LOOKUP(sq,pr)))

without LET function

Please Login or Register  to view this content.

[danjackson5]

@alansidman

Thank you. I'll have a study and get back to you. Appreciate the help.

Dan

[danjackson5]

@Bo_Ry

Thank you. Appreciate this but could you break it down for me so I can adapt it for my much larger spreadsheet? cheers

[AliGW]

Which 'it' do you mean? The LET formula or the WORKDAY formula?

[danjackson5]

Its ok, I think I've worked it out. I'm obviously an intermediate at best when working with these longer complicated functions! I've been able to work it into my larger sheet and am now trying my phase 2. Thanks

[AliGW]

Great, but we still don't know which 'it' it is. Did you use the LET formula or the WORKDAY formula?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[danjackson5]

I went with the WORKDAY one in the end - put it in the spreadsheet and adapted the ranges to suit.

+Rep added for all contributors and will mark thread as solved now. Thanks again all

Best wishes
Dan

[danjackson5]

=================
Hold off on replies - just found this works in a new sheet so the problem is somewhere else. I'll mark as solved until i've investigated further, thank you
=================

Hi,

The project is going great but i have one tiny problem with the formula provided. I know its just a character somewhere but just find it... Could anyone show me which one it is?

The formula pulls the working days from the table below but is missing the very last day. I wish for it to include the beginning day as well as the last day.

Please Login or Register  to view this content.

	C	D
19	01/03/2021	03/03/2021
20	01/06/2021	05/06/2021
21	26/07/2021	30/07/2021

Generates This list (Minus any weekends of course)

01/03/2021
02/03/2021
03/03/2021
01/06/2021
02/06/2021
03/06/2021
04/06/2021
05/06/2021
26/07/2021
27/07/2021
28/07/2021
29/07/2021
30/07/2021 - This is the date that is missing that needs to be included.


As always, thank you for your assistance

Dan

=================
Hold off on replies - just found this works in a new sheet so the problem is somewhere else. I'll mark as solved until i've investigated further, thank you
=================

Edited 28/07/2021 11.52 Placing on hold for further investigation

[AliGW]

Attach a fresh workbook. You might also want to remove the SOLVED tag for now.

[danjackson5]

Hi,

Found the issue and have posted a sample spreadsheet. It appears one of the date ranges isn't registering on the list in column CA but i cant figure out why - no rows have been deleted or hidden and identical sheets on the main workbook are unaffected. Any ideas? Thanks

Edited 28/07/2021 12.24 - fixed some typos