Forum Discussion
Creating a formula reference based on cell values
I have created a calendar of working days running horizontally with 1 for a working day and 0 for a non-working day
In a separate row I have dates running horizontally.
1 1 1 0 0 1 etc.
5/12 6/12 7/12 8/12 9/12 10/12
I have a start date and an end date and want to sum the total number of workdays.
I can use the Match function to match the date and locate the applicable start and finish columns i.e. column 5 and 61, but cannot figure how to apply this information to sum the number of working days.
CPBExcelnovice That could be a formula like in the picture below.
Note that I worked with named ranges for days and dates. This, to avoid direct cell references which would make this formula more difficult to write, read and maintain.
But perhaps you can use one of the NETWORKDAYS or NETWORKDAYS.INTL functions.
2 Replies
CPBExcelnovice That could be a formula like in the picture below.
Note that I worked with named ranges for days and dates. This, to avoid direct cell references which would make this formula more difficult to write, read and maintain.
But perhaps you can use one of the NETWORKDAYS or NETWORKDAYS.INTL functions.
- Thank you.
This has answered the hidden question for me, which was how to develop a formula based on cell values. I originally dismissed the idea of using INDEX as I figured that I already knew the row...but of course it is all clear to me now!
