What is this formula doing?

[coppercarbide]

Hi all,

I made a sheet a long time ago and need to modify it a bit. One of the cells calculates how many work days are within two days, I had some help doing this one, and I'm using this formula:

=IF(J6="",0,SUM(INT((WEEKDAY(J6-{2,3,4,5,6,7})+K6-J6)/7)))

I need to modify this formula in order to be able to dynamically set whether Saturday is a workday or not (currently it is in the above formula).

If someone could explain what exactly this formula is doing, I should hopefully be able to figure out how to dynamically select the workdays.

Thanks!

 

[coppercarbide]

I think I actually ended up finding a different solution, which I'll post in case someone stumbles upon this in the future.

What I was trying to do is to make a formula that counts the work days between two dates. This would normally be achieved with the networkdays function, but I wanted the ability to specific Saturday as a workday, and the flexibility to specificy that Sunday is NOT a work day, AND to have holidays.

First I build a matrix that will be user-modifiable in the model.
It looks like this:
Sunday 1 FALSE
Monday 2 TRUE
Tuesday 3 TRUE
(and so on... the user only modifies the true or false tags as to whether that is a workday)

I then made another sheet with the dates down column A. (this is called "alldates")
Jan 1, 2011
Jan 2, 2011
Jan 3, 2011

In column B I made the following formula: =VLOOKUP(WEEKDAY(A2),Variables!$B$15:$C$21,2,FALSE)
This returns a true or a false as to whether that day is a work day.

I then manually went in and changed the dozen holidays per year to False. Not elegant, but it works.

Finally, in my production schedule I have a start date and an end date for the job (cell F3 and G3)

I then made a cell for "Number of Work Days", and made this formula:
=COUNTIF(INDIRECT("B"&MATCH(F3,alldates)+1&":B"&MATCH(F3,alldates)+1+(G3-F3)),TRUE)

This formula simply counts how many cells are "TRUE" between the start date and the end date. The +1 in there is because my date column starts at A2, not A1.

While certainly not elegant, this does seem to work for what I need. It's flexible, it's modifiable by the end user, and it seems to be technically correct.

If anyone has a more elegant solution, please let me know. Also... I'm still wondering how the hell the above formula works.

 

[Dryver14]

have you used
click on the cell with the formula then

alt key and T U F and this will take you through formulas step by step........may help

 

[T. Valko]

Hi all,

I made a sheet a long time ago and need to modify it a bit. One of the cells calculates how many work days are within two days, I had some help doing this one, and I'm using this formula:

=IF(J6="",0,SUM(INT((WEEKDAY(J6-{2,3,4,5,6,7})+K6-J6)/7)))

I need to modify this formula in order to be able to dynamically set whether Saturday is a workday or not (currently it is in the above formula).

If someone could explain what exactly this formula is doing, I should hopefully be able to figure out how to dynamically select the workdays.

Thanks!

The "guts" of that formula is counting how many Monday thru Saturday there are between 2 dates (inclusive).

The 7 in the array {2,3,4,5,6,7} represents Saturday.

To be honest, I don't know what logic is being applied but I fully understand the "mechanics".

Many years ago I asked a similar question:

Can anyone explain HOW that formula works?

No one replied!

So, when I reply to questions of this nature I almost always suggest an alternative formula that I can explain if asked!

=SUM(INT((WEEKDAY(J6-{2,3,4,5,6,7})+K6-J6)/7))

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J6&":"&K6)),2)<7))

 

[barry houdini]

=IF(J6="",0,SUM(INT((WEEKDAY(J6-{2,3,4,5,6,7})+K6-J6)/7)))

This derives from this formula which counts the Mondays between J6 and K6 (inclusive)

=INT((WEEKDAY(J6-2)+K6-J6)/7)

The 2 can be changed to 3 for Tuesday, 4 for Wednesday etc. or you can use an array, as you have, to count any combination of days. In your version taking out the 7 from {2,3,4,5,6,7} would exclude Saturdays. To make it dynamic you could use this version

=SUMPRODUCT(INT((WEEKDAY(J6-{1;2;3;4;5;6;7})+K6-J6)/7),Z$1:Z$7+0)

where Z1:Z7 contains TRUE/FALSE for Sun to Sat depending on whether those days should be included. That doesn't account for holidays so for that you can add another element, e.g.

=SUMPRODUCT(INT((WEEKDAY(J6-{1;2;3;4;5;6;7})+K6-J6)/7)-FREQUENCY(IF((H$1:H$10>=J6)*(H$1:H$10<=K6),WEEKDAY(H$1:H$10)),{1;2;3;4;5;6}),Z$1:Z$7+0)

where holiday dates are listed in H1:H10

This version is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER

If you have Excel 2010 you can do everything more easily using NETWORKDAYS.INTL function where the "weekend" days can be customised....

 

[barry houdini]

Can anyone explain HOW that formula works?

Hello Biff,

I believe I "invented" that formula so I really ought to know how it works – I certainly do but I’ve never perhaps explained it as well as I could so here's my best attempt……

Starting with this version

=INT((WEEKDAY(J6-2)+K6-J6)/7)

That counts Mondays between J6 and K6 inclusive – assumes K6 >= J6
The idea that I started with was that there was no need to examine every date in a range to see how many of those were Mondays – I knew that if there were 7 days in the range there would be 1 Monday – if there were 14 days then there must be 2. Now if the range wasn’t divisible by 7 then it was trickier – if there were 8 days in the range then there would be 1 Monday or 2……which one depends on the start date (or end date). In fact if there were 8 days in the range then there could only be 2 Mondays if the start date was a Monday….if there were 9 days in the range then there could only be two Mondays if one of the first two days was a Monday (i.e. start date should be Sunday or Monday)…..if there were 10 days in the range then start date would need to be Saturday, Sunday or Monday…….etc.

So I can quantify that with a formula, if I subtract the start from the end and add 1 to get the number of days….then divide by 7 and round down that will give me the minimum number of Mondays, i.e.

=INT((1+K6-J6)/7)

But now I need to factor in an adjustment based on start date, by having a Monday start date I have a whole week’s worth of Mondays in one day (effectively), so I need to add another 6 when Monday is the start date…..if Sunday is the start date I want to add another 5, if Saturday is the start date I add 4…..so to do that (combined with the +1 already in there) I can use the WEEKDAY function offset accordingly, e.g. WEEKDAY(J6-2) will give me 7 on a Monday, 6 on a Sunday etc….so the formula becomes:

=INT((WEEKDAY(J6-2)+K6-J6)/7)

[Another way to look at this is that you are effectively expanding the size of the date range backwards to start immediately after the previous Monday....by doing that you never add any Mondays but you "normalise" the date range so that it always starts on the same day of the week.........]

Clearly 2 works for a Monday, change that to 1 for Sun through to 7 (or 0) for Sat…and you can use any combination in an array and then SUMMED, e.g. to count Mondays and Fridays in the range

=SUM(INT((WEEKDAY(J6-{2,6})+K6-J6)/7))

Note that J6 is repeated twice in the formula, K6 only once, so if you have some sort of complex formula to calculate the start date….and a less complex one for end date then it might be a good idea to use a formula that will calculate based on the weekday of the end date (K6), so this formula uses a similar logic to count Mondays between J6 and K6

=INT((8-WEEKDAY(K6-1)+K6-J6)/7)

regards, barry

 

[T. Valko]

Hello Biff,

I believe I "invented" that formula so I really ought to know how it works – I certainly do but I’ve never perhaps explained it as well as I could so here's my best attempt……

Starting with this version

=INT((WEEKDAY(J6-2)+K6-J6)/7)

That counts Mondays between J6 and K6 inclusive – assumes K6 >= J6
The idea that I started with was that there was no need to examine every date in a range to see how many of those were Mondays – I knew that if there were 7 days in the range there would be 1 Monday – if there were 14 days then there must be 2. Now if the range wasn’t divisible by 7 then it was trickier – if there were 8 days in the range then there would be 1 Monday or 2……which one depends on the start date (or end date). In fact if there were 8 days in the range then there could only be 2 Mondays if the start date was a Monday….if there were 9 days in the range then there could only be two Mondays if one of the first two days was a Monday (i.e. start date should be Sunday or Monday)…..if there were 10 days in the range then start date would need to be Saturday, Sunday or Monday…….etc.

So I can quantify that with a formula, if I subtract the start from the end and add 1 to get the number of days….then divide by 7 and round down that will give me the minimum number of Mondays, i.e.

=INT((1+K6-J6)/7)

But now I need to factor in an adjustment based on start date, by having a Monday start date I have a whole week’s worth of Mondays in one day (effectively), so I need to add another 6 when Monday is the start date…..if Sunday is the start date I want to add another 5, if Saturday is the start date I add 4…..so to do that (combined with the +1 already in there) I can use the WEEKDAY function offset accordingly, e.g. WEEKDAY(J6-2) will give me 7 on a Monday, 6 on a Sunday etc….so the formula becomes:

=INT((WEEKDAY(J6-2)+K6-J6)/7)

[Another way to look at this is that you are effectively expanding the size of the date range backwards to start immediately after the previous Monday....by doing that you never add any Mondays but you "normalise" the date range so that it always starts on the same day of the week.........]

Clearly 2 works for a Monday, change that to 1 for Sun through to 7 (or 0) for Sat…and you can use any combination in an array and then SUMMED, e.g. to count Mondays and Fridays in the range

=SUM(INT((WEEKDAY(J6-{2,6})+K6-J6)/7))

Note that J6 is repeated twice in the formula, K6 only once, so if you have some sort of complex formula to calculate the start date….and a less complex one for end date then it might be a good idea to use a formula that will calculate based on the weekday of the end date (K6), so this formula uses a similar logic to count Mondays between J6 and K6

=INT((8-WEEKDAY(K6-1)+K6-J6)/7)

regards, barry

That highlighted part is what I couldn't figure out before.

Very good!

 

[Dryver14]

So would the answer to the original question be to remove the 7 from the array and divide by 6?

Or indeed is the point it can not be used to work out "short weeks"?

 

[T. Valko]

So would the answer to the original question be to remove the 7 from the array and divide by 6?

Or indeed is the point it can not be used to work out "short weeks"?

It depends on how the OP wants to make Saturday be a variable day.

I was think about using an IF like this:

To include Saturdays in the count enter a "X" in cell L6.

Array entered**:

=SUM(INT((WEEKDAY(J6-IF(L6="x",{2,3,4,5,6,7},{2,3,4,5,6}))+K6-J6)/7))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Normally entered:

=SUMPRODUCT(INT((WEEKDAY(J6-IF(L6="x",{2,3,4,5,6,7},{2,3,4,5,6}))+K6-J6)/7))

I also like Barry's other formula for "selectable" weekdays.

=SUMPRODUCT(INT((WEEKDAY(J6-{1;2;3;4;5;6;7})+K6-J6)/7),Z$1:Z$7+0)

where Z1:Z7 contains TRUE/FALSE for Sun to Sat depending on whether those days should be included.

 

[Dryver14]

Thanks Biff,

Another one for my collection of looks brilliant but need a week to play with it formulas......so I can pull it from the bag and look smart one day,

I will of course say "I" invented it.

Regards