Number of Fridays between two dates

by | Dec 6, 2022 | Excel

Some­times, we want to count a cer­tain num­ber of week­days between two dates. If we want to count work­days, we need to use the NETWORKDAYS func­tion. But, if we want to count the num­ber of, let’s say Fri­days, we have to use the NETWORKDAYS.INTL func­tion. We start with the start date, fin­ish with the end day, and then we mod­i­fy week­ends. In the Excel help descrip­tion, we can see that we can change work­ing days and days off using a text string of 1s and 0s, where 1 means a day off and 0 means a work­ing day. So, Mon­day, Tues­day, Wednes­day, Thurs­day are 1, Fri­day is 0, Sat­ur­day and Sun­day are 1 (Fig. 1).

Num­ber of Fri­days between two dates

=NETWORKDAYS.INTL(A2,B2,“1111011”)

NETWORKDAYS.INTL function
Fig. 1 NETWORKDAYS.INTL function

Here, we have the num­ber of Fri­days between two dates. It’s impor­tant that the func­tion also con­sid­ers the start date and the end date in its cal­cu­la­tions (Fig. 2). 

Number of Fridays
Fig. 2 Num­ber of Fridays

The NETWORKDAYS.INTL func­tion can also work with hol­i­days. Now, only Fri­day hol­i­days are impor­tant for us, so let’s select the cell and press the F4 key to lock it (Fig. 4).

=NETWORKDAYS.INTL(A2,B2,1111011”,$F$3:$F#4)

Holidays on Friday
Fig. 3 Hol­i­days on Friday

And we have our results (Fig. 4). 

Results
Fig. 4 Results

Sum­ming up, 0 means a work­ing day, and 1 means a day off in the sev­en-num­ber text string. If we write 0 also in the fourth place, it means that Thurs­day and Fri­day are work­ing days (Fig. 5). 

=NETWORKDAYS.INTL(A2,B2,1110011”,$F$3:$F#4)

Two working days
Fig. 5 Two work­ing days

As we can see, this small change mod­i­fied our results once again (Fig. 6). 

Modified results
Fig. 6 Mod­i­fied results

https://www.youtube.com/watch?v=6qrKix6QxOs

Date after X work days

by | Dec 2, 2022 | Excel

If you want to find a date after a cer­tain num­ber of work­days, you can use the WORKDAY func­tion. You just need the start date and the num­ber of work­ing days (Fig. 1). 

Date after X work days
WORKDAY function
Fig. 1 WORKDAY function

We can see that Excel shows the date after the cor­re­spond­ing num­ber of work­ing days. You have to remem­ber that the WORKDAY func­tion does­n’t con­sid­er the start date in its cal­cu­la­tions. It means that if you have only one work­day, you will go to the next work­ing day (Fig. 2). 

Next working day
Fig. 2 Next work­ing day

In the WORKDAY func­tion, you can even add hol­i­days by just select­ing a range with prop­er dates. Remem­ber to lock it (F4) (Fig. 3). 

Adding holidays
Fig. 3 Adding holidays

We can see that our dates are a bit changed (Fig. 4). 

Dates changed
Fig. 4 Dates changed

The WORKDAY func­tion con­sid­ers the week­end as Sat­ur­day and Sun­day, which is cor­rect in most of the coun­tries, how­ev­er we can mod­i­fy it. When we want only Sun­day to be our week­end, we have to choose the WEEKDAYS.INTL func­tion. The func­tion con­sists of the start date and the num­ber of work­ing days. The third argu­ment is the week­end, where we can change the week­end days. Let’s choose Sun­day (Fig. 5). 

Sunday as the weekend
Fig. 5 Sun­day as the weekend

Then, we are choos­ing hol­i­days by select­ing a prop­er range and lock­ing it (F4 key). Our for­mu­la is ready (Fig. 6). 

=WORKDAY.INTL(A2,B2,11,$F$2:$F$3)

Our formula
Fig. 6 Our formula

We can see that our results dif­fer from the the pre­vi­ous ones because now Sat­ur­day is our work­ing day (Fig. 7). 

Saturday as a working day
Fig. 7 Sat­ur­day as a work­ing day

Number Of Working Days Between Two Dates

by | Dec 1, 2022 | Excel

When we want to count the num­ber of work­ing days between two dates we can use the NETWORKDAYS func­tion. The func­tion is very sim­ple, as it needs only the start and end dates. And that’s it (Fig. 1). 

Num­ber Of Work­ing Days Between Two Dates

=NETWORKDAYS(A2,B2)

NETWORKDAYS function
Fig. 1 NETWORKDAYS function

And we have the num­bers of work­ing days between two dates. It’s impor­tant that the NETWORKDAYS func­tion con­sid­ers also the start and end days. If the start and end days are the same, the num­ber will tell us whether the day is a work­ing day or not (Fig. 2). 

Working days
Fig. 2 Work­ing days

We can also add hol­i­days to this func­tion in the third argu­ment. We just have to select a range with prop­er dates and press the F4 key to lock it (Fig. 3).

=NETWORKDAYS(A2,B2,$F$2:$F$3)

Holidays
Fig. 3 Holidays

Now, we can see that the num­ber of days changed due to those hol­i­days (Fig. 4). 

Changed numbers
Fig. 4 Changed numbers

In the NETWORKDAYS func­tion, the week­end is con­sid­ered as Sat­ur­day and Sun­day. How­ev­er, we can mod­i­fy it by using the NETWORKDAYS.INTL func­tion. The syn­tax of those two func­tions is almost the same. Apart from the start and end day, we can choose what our week­end days will be. Let’s choose Sun­day only (Fig. 5). 

NETWORKDAYS.INTL function
Fig. 5 NETWORKDAYS.INTL function

Then, we can add hol­i­days if we want. Let’s select the prop­er hol­i­day range and press the F4 key to lock it (Fig. 6). 

=NETWORKDAYS.INTL(A2,B2,11,$F$2:$F$3)

Function with holidays
Fig. 6 Func­tion with holidays

And we have our results. We can see that the num­ber of work­ing days between the dates grew because Sat­ur­day is a work­ing day (Fig. 7). 

Working days without Sundays
Fig. 7 Work­ing days with­out Sundays

SUMIFS AVERAGEIFS and COUNTIFS functions SUM with conditions

by | Nov 15, 2022 | Excel

Today, we are count­ing sums, aver­ages and num­ber of trans­ac­tions with more than one con­di­tion. In our exam­ple, we have trans­ac­tion sales table and we want to count the val­ues for giv­en months. How­ev­er, the months are writ­ten as the first day of the month and the last day of the month. It means that we have to add two con­di­tions in order to count it. Let’s start with sums. We can use the SUMIFS func­tion. The first argu­ment of this func­tion is a range on which we will be sum­ming. In our case it’s the sales col­umn so we have to select cell C2 and press the Ctrl + Shift + ↓ com­bi­na­tion, then the F4 key to lock it (Fig. 1).

=SUMIFS($C$2:$C$1193

SUMIFS AVERAGEIFS and COUNTIFS func­tions SUM with conditions
SUMIFS function with a locked range
Fig. 1 SUMIFS func­tion with a locked range

We write a com­ma, and then we are going to the cri­te­ria range 1. This will be the range where we will be check­ing the first cri­te­ri­on, which is the third argu­ment of the SUMIFS func­tion. In our case, we are check­ing dates, so let’s press cell A2, Ctrl + Shift + ↓, then the F4 key. We are adding a com­ma again, and now we have to write the first con­di­tion, which is the first cri­te­ri­on. The order of our con­di­tions is not impor­tant because, in the end, all con­di­tions have to be met to count the thing we want. Let’s start with the From date. We want val­ues that are big­ger or equal to this date, which means that we have to write the sym­bols of larg­er than and equal in dou­ble quotes, then an amper­sand because we are com­bin­ing this text with the val­ue from cell E2 (Fig. 2).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2

SUMIFS with the first condition
Fig. 2 SUMIFS with the first condition

After press­ing the F9 key, we can see that Excel changed the date into a num­ber. This makes Excel check our con­di­tion prop­er­ly (Fig. 3).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,“>=43831”

Excel changed the dated
Fig. 3 Excel changed the dates

How­ev­er, let’s move back to pre­vi­ous text by press­ing Ctrl + Z. Now, we have the first con­di­tion. We can see that the sec­ond con­di­tion is nec­es­sary with sec­ond critiri­on because the next two argu­ments are in square brack­ets (Fig. 4).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2

Arguments in square brackets
Fig. 4 Argu­ments in square brackets

Cri­te­ria range 2 in our case is the same as cri­te­ria range 1, so we can just copy it (Ctrl +C, Ctrl +V), but we real­ly need to put it because each cri­te­ria range is work­ing just with the argu­ment of its cri­te­ria. It means that cri­te­ria range 1 is work­ing with cri­te­ria 1, and cri­te­ria range 2 is work­ing with cri­te­ria 2. In our case, the cri­te­ria 2 are num­bers small­er than or equal to the last day of the month in cell F2. 

Now, we have the whole for­mu­la (Fig. 5). 

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2,$A$2:$A$1193,”<=”&F2)

Whole SUMIFS formula
Fig. 5 Whole SUMIFS formula

When we want to sum the val­ues from the sum range, the first and the sec­ond con­di­tion have to be met. Let’s put the for­mu­la and copy it down. We can see that at the end every­thing is work­ing prop­er­ly because we have writ­ten the for­mu­la prop­er­ly (Fig. 6).

Proper results
Fig. 6 Prop­er results

We still have some more val­ues to count. Let’s take the aver­ages. Since I’m a lazy per­son, I will copy the whole for­mu­la in edit mode, then go to the Aver­age col­umn, paste it and change only the name of SUMIFS into AVERAGEIFS. After enter­ing it, we can see that only the sum range changed its name into aver­age range. The remain­ing argu­ment names stayed the same. It’s because the SUMIFS and the AVERAGEIFS for­mu­las have the same syn­tax, but they count sums and aver­ages cor­re­spond­ing­ly (Fig. 7). 

Argument name change
Fig. 7 Func­tion name change

Let’s put the for­mu­la into our cell and copy it down. We have the results (Fig 8).

SUMIFS reults
Fig. 8 SUMIFS reults

Let’s now count the num­ber of trans­ac­tions. We will use the COUNTIFS func­tion. This func­tion does­n’t have any range on which we could count sums, aver­ages and so on. It only needs cri­te­ria ranges and cri­te­ria. Our first cri­te­ria was the one with dates. So, let’s write A2, then press Ctrl + Shift + ↓, then the F4 key to lock it and a com­ma. I want to show you that the order of con­di­tions is not impor­tant. Let’s take val­ues small­er that or equal to the date in cell F2, com­ma, then the same cri­te­ria range, then the sec­ond cri­te­ria will be greater than or equal to the val­ue from cell E2. Those are all the argu­ments we need for a COUNTIFS func­tion (Fig. 9).

=COUNTIFS($A$2:$A$1193,”<=”&F2,$A$2:$A$1193,”>=”&E2)

COUNTIFS formula
Fig. 9 COUNTIFS formula

After copy­ing the for­mu­la down, we have the results. We can check if our aver­age is cor­rect just by look­ing at it because it is just the sum divid­ed by the num­ber of trans­ac­tions (Fig. 10).

Checking the results
Fig. 10 Check­ing the results

We can see that the num­bers are exact­ly the same as in the Aver­age col­umn (Fig. 11). 

The same numbers
Fig. 11 AVERAGEIFS and divide gives the same results

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

Random list no repeats, manual and formulas solutions

by | Nov 2, 2022 | Excel

Today, we are going to talk about choos­ing a ran­dom ele­ment from a list with­out repeats. We have eight ele­ments in our list. I put there the same num­ber two times, which gives me the oppor­tu­ni­ty to choose the same num­ber two times. If there are three the same num­bers, I could choose the num­ber up to three times. How­ev­er, the choos­ing is ran­dom, so I don’t real­ly know how many times I will choose it. 

Ran­dom list no repeats, man­u­al and for­mu­las solutions
A list of elements
Fig. 1 A list of elements

If we want to choose an ele­ment from the list at ran­dom with­out repeats, we can use a man­u­al solu­tion. We need to write the RAND func­tion which returns num­bers from 0 to 1 with fif­teen dig­it pre­ci­sion. It means that it’s almost impos­si­ble to choose the same num­ber twice (Fig. 2). 

=RAND()

RAND function
Fig. 2 RAND function

After drag­ging for­mu­la down, we have 8 ran­dom num­bers. We have to remem­ber that the RAND func­tion is a volatile func­tion. It means that it is recal­cu­lat­ed every time our work­sheet changes (Fig. 3).

Recalculation
Fig. 3 Recalculation

Now, that we have our helper col­umn, we can do the sort­ing. We go to Data tab and we choose the A to Z or Z to A command.

Sorting
Fig. 4 Sorting

If we want to have, e.g. five ele­ments, we have to choose five ele­ments from the top. It does­n’t mat­ter whether we sort from A to Z or Z to A. We just choose the first five ele­ments. We can see that the num­bers are recal­cu­lat­ed every time we sort our data (Fig. 5)

Five elements from the list
Fig. 5 Five ele­ments from the list

Let’s get back to our orig­i­nal list and try the sec­ond solu­tion. It is from Lega­cy Excel and it uses for­mu­las. Let’s start with a way to sort our num­bers. We can use, e.g. the LARGE func­tion, which returns the biggest num­ber from the list, then the sec­ond biggest, then the third biggest, and so on. We have to press the F4 key to lock our list. Then, we need a way to choose the first, the sec­ond, and so on num­bers. So, we want to change our key argu­ment using a prop­er func­tion. One of the sim­plest solu­tion I know is using the ROW func­tion with ref­er­ence to cell A1, which is free (with­out $ signs). When we copy our for­mu­la down, our A1 ref­er­ence will also go down, and the ROW func­tion will return the first row, the sec­ond, the third, and so on (fig. 6)

=LARGE($B$2:$B$9,ROW(A1))

ROW function
Fig. 6 ROW function

After enter­ing the for­mu­la, we have our num­bers placed from the largest to the small­est. Our five ele­ments are now in a prop­er order. It means that now we can choose ele­ments. How­ev­er, we must use the MATCH and INDEX func­tion in our data ori­en­ta­tion. Let’s add the MATCH func­tion to find the posi­tion of our num­ber. We have to add the range, which is our ran­dom num­ber col­umn and press the F4 key to lock it. Then, we are adding 0 because we want to have the exact match (Fig. 7)

=MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0)

MATCH function
Fig. 7 MATCH function

After enter­ing and copy­ing down, we have posi­tions of the largest num­bers. When we look at the first posi­tion in our Cho­sen col­umn, we have 5. It means that the largest num­ber from our orig­i­nal list is locat­ed in the 5th place. 

Position of the largest number
Fig. 8 Posi­tion of the largest number

How­ev­er, we don’t want to have posi­tion num­bers, but the ele­ments con­nect­ed to those num­bers. It means that we have to add the INDEX func­tion to our MATCH func­tion. This time, we want to choose ele­ments from our list, so we are adding the range and lock it by press­ing the F4 key. It turns out that our MATCH func­tion is the row num­ber. We have to close the for­mu­la (Fig. 9)

=INDEX($A$2:$A$9,MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0))

INDEX function
Fig. 9 INDEX function

After enter­ing and copy­ing down, we have ele­ments select­ed at ran­dom with­out repeats. When I press the F9 key, I am forc­ing it to recal­cu­late our results. Some­times, we see our num­ber (123) twice because it is twice on our orig­i­nal list (Fig. 10)

Elements selected at random
Fig. 10 Ele­ments select­ed at random

It’s high time we tried the final solu­tion in Dynam­ic Array Excel. This solu­tion is sim­pler that the one in Lega­cy Excel because we just need two func­tions. In DA Excel, we have the RANDARRAY func­tion which can cre­ate a ran­dom array. We just need to copy the same col­umn into our for­mu­la, which means that we need an array with eight rows and one col­umn. That’s why we are writ­ing just 8 (Fig. 11).

=RANDARRAY(8)

RANDARRAY function
Fig. 11 RANDARRAY function

After putting our for­mu­la into Excel, we have eight ran­dom num­bers. In DA Excel, we can sort by this col­umn. Thus, we have to add the SORTBY func­tion to our for­mu­la. The array which we want to sort is our list. This time, we don’t have to lock it because it is a DA for­mu­la, and our for­mu­la is the result (Fig. 12). 

=SORTBY(A2:A9,RANDARRAY(8))

SORTBY function
Fig. 12 SORTBY function

After putting the for­mu­la down, we have eight ele­ments from our list in a ran­dom order. Press­ing the F9 key allows us to choose ele­ments once again at ran­dom. Let’s choose five ele­ments from the top once again. We still can see the remain­ing ele­ments. If you don’t want to see them, you have some options. You can, for exam­ple, select five ele­ments from our list, then press the F2 key to go to the for­mu­la edit mode, then press the Ctrl + Shift + Enter com­bi­na­tion to cre­ate an array for­mu­la. The array for­mu­la is from Lega­cy Excel. Now, we have only five ele­ments because I’ve select­ed only five cells. Those cells are an array, which means that we can­not change or delete any sin­gle ele­ment of the array. If you want to change it, you have to change the whole array at one time (Fig. 13).

An array
Fig. 13 An array

Sum­ming up, if you want to choose an ele­ment at ran­dom from a list, you can use a man­u­al solu­tion, a Lega­cy Excel solu­tion or a Dynam­ic Array solution. 

https://www.youtube.com/watch?v=myT-vQzDSeE