We can count the number of spaces between words. Let’s have a look at our first example. In cell A2, we have spaces between words and at the end of each line, so the case is simple. We can use the LEN function here that counts the length of text (Fig. 1).
=LEN(A2)
Fig. 1 LEN function
Now, that we have the number of signs, we can remove spaces. We can use the SUBSTITUTE function here. This function will change the old text, which in our case is a space into new text. And since we want to remove spaces, our new text will be an empty text string written in double quotes (Fig. 2).
=SUBSTITUTE(A2,” “,“”)
Fig. 2 SUBSTITUTE function
We can see that the function removed all spaces from our text (Fig. 3).
Fig. 3 No spaces
Now, we can count the length of the text without spaces using the LEN function (Fig.4).
=LEN(SUBSTITUTE(A2,” “,“”)
Fig. 4 Counting the text length
Now we know the length (Fig. 5).
Fig. 5 Text length
Since we know how many spaces there are, we can use the formula to count the words. We just have to subtract this formula from the previous one (Fig. 6).
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))
Fig. 6 Subtracting formulas
And we have the number of spaces (Fig. 7).
Fig. 7 Number of spaces
We still have to remember that to count the number of words, we have to add 1 because there is only one space between two words (Fig. 8).
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))+1
Fig. 8 One space between two words
Now we know the number of words in each cell.
Fig. 9 Number of words
If we want to count words from all cells, we can use almost the same formula. The only thing we have to change is the range which now will be from A2 to A4 (Fig. 10).
=LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,” “,“”))+1
Fig. 10 Adding a new range
In Dynamic Array Excel, the results will be spilled, however we can sum the up (Fig. 11).
Fig. 11 Split results
In the previous version of Excel we can use the the SUMPRODUCT function (Fig. 12).
When we want to count the number of working hours in Excel, we have z problem with night shifts. Let’ start with a human approach and day shift.
Working hours and wages. Night shifts | Excel Tips #28
In a day shift, the end time is larger that the start time, so we can just subtract one number from another (Fig. 1).
=(B2-A2)
Fig. 1 Subtracting one number from another
After entering and copying down, we can see we have proper results for day shifts. In night shifts, when the end time is smaller that the start time, it does’t show us any time, only hashtags. It happened so because Excel refused to show any negative dates or time. Even if Excel showed us negative results, they would be wrong (Fig. 2).
Fig. 2 No negative time
First, we have to remember that Excel stores time as numbers, i.e. the part of the day that has passed. When we look at the first hour, which is 8 am, it means that one-third of the day has passed. When we go to the Time bar, and press General, we can see that the time will be given in decimal numbers (Fig. 3), (Fig. 4).
Fig. 3 General Time Fig. 4 Time in decimal numbers
When we want to convert Excel time into human time, we have to multiply it by 24 (Fig. 5).
=(B2-A2)*24
Fig. 5 Excel time into human time
Now, we can see that the we can easily read the values. Remember to have the General option selected. There are negative values for the night shift. What’s more, they are wrong, as there are only 8 hours from 8 pm to 4 am in row number 3, not 16.
Fig. 6 Negative and wrong results
It means that this human approach won’t work in Excel, so let’ delete the results. What can work here is a function called MOD, which is also quite short. In the MOD function, we also use the human approach, which means that from the end time we subtract the start time. However, the MOD function needs a divisor, i.e. 1, which means that it is one whole day (Fig. 7)
=MOD(B2-A2,1)
Fig. 7 Divisor
Now, we can see that our results in day and night shifts are correct. We can finally calculate the wages by multiplying the number of working hours by the hourly wage (Fig. 8).
=C2*$F$2
Fig. 8 Calculating wages
As we can see the results are wrong again. Let’s have a look at row number two. There are 8 hours, but Excel doesn’t multiply 8 hours times 40$, but one third of a day times 40$, which gives us around $13 (Fig. 9).
Fig. 9 Wrong results
We want to have proper wages, so we need to multiply it by 24 hours (Fig. 10).
=C2*$F$2*24
Fig. 10 Multiplying by 24
Now, we have proper wages (Fig. 11).
Fig. 11 Proper wages
If you don’t need precision bigger than 15 minutes, just use numbers instead of Excel time. I’m advising it to you from my own experience.
Sometimes, we want to find all the matched values and combine them into one text string, i.e., we want o VLOOKUP all matched values and then combine them.
VLOOKUP all matches and combine them
I will give you two solutions. The first solution will work from Excel 2019, and the second one will work in Dynamic Array Excel. First of all, we have to check if our representative comes from the country we are looking at. Let’s take a representative from Spain. There is only one representative of Spain in our example. We can check it using the IF function, where we have to create a proper logical test. We have to take into consideration the Country column, so let’s write it by selecting the column and clicking the F4 key to lock it. Then, we have to check if the values from the column are equal to just one country (Spain) by clicking the cell with the name of Spain (D4). It means that cell D4 will be compared to each cell from the Country column individually (Fig. 1)
Fig. 1 Comparison to each cell
The logical test will return TRUE only in the case of Spain. In the case of other countries, it will return FALSE. If the logical test is in an array, we can choose proper, corresponding result as a return value. We just need to select an array of the same size, which, in our case, is the Representative column. Then, we click the F4 key to lock it. When the logical test returns FALSE, we want to see an empty text string, so we have to write a space in double quotes. Let’s close the function with a parenthesis (Fig. 2)
=IF($A$2:$A$14=D2,$B$2:$B$14,” ”)
Fig. 2 IF function
Since I’m working in Dynamic Array Excel, Excel spills the results to the whole column (Fig. 3).
Fig. 3 IF function results
We can see that we have only one representative of Spain, and many empty cells. If we want to find the remaining representatives, we have to join the information we have. To do that, we can use the TEXTJOIN function, which is available from Excel 2019. We have to give the function a delimiter. Let’s write a comma and a space in double quotes. Then, we have to decide whether we want to ignore empty cells. We definitely do, so let’s write TRUE, or its shorter version, which is 1. Then, we just have to close the whole formula once again (Fig. 4).
After entering the formula and copying it down, we have our results (Fig. 5).
Fig. 5 TEXTJOIN function results
When we are in Dynamic Array Excel, we can use the FILTER function instead of the IF function. This function just filters our data. Let’s write FILTER function. Our data is located only in one column, which is the Representative column, so we have write the range and lock it with the F4key. Then, we have to create an array of the same size with logical values of TRUE or FALSE. It means that we have to create the same logical test as we did with the IF function. Let’s add the Country column (A2:A14), F4key to lock it, then an equal sign to check whether any of the value from the Country column is equal to Spain (D2). After closing it, we have a ready formula (Fig. 6).
=FILTER($B$2:$B$14,A$2:$A$14,=D2)
Fig. 6 FILTER function
We can see that Spain has only one representative, but Poland has got three (Fig. 7).
Fig. 7 FILTER function results
That’s why we have to put our FILTER function results into the TEXTJOIN function. And, once again, we have to write a comma and a space in double quotes. Then, we have to decide that we want to ignore empty cells and close the whole formula (Fig. 8)
=TEXTJOIN(“, “,1,FILTER($B$2:$B$14,A$2:$A$14=D2))
Fig. 8 TEXTJOIN with FILTER function
We can see that the results in the first solution and in the second solution are the same (Fig. 9)
Sometimes, we want to highlight rows of weekends. In such a case, we can use conditional formatting but, first of all, we have to create a formula which will return TRUE for Saturday and Sunday, i.e. weekend days. We can start with the WEEKDAY function which will return the number of days in a week (Fig.1).
Highlight weekends with conditional formatting
=WEEKDAY(A2)
Fig. 1 WEEKDAY function
We have our results. However, this numeration assigns number 7 to Saturday, and number 1 to Sunday. It’s not proper from our perspective. We have to modify it by changing the week day number sequence. The best option we can choose is number 1 for Mondays and number 7 for Saturdays. That’s why we have to write 2 in the second WEEKDAY function argument (Fig.2).
=WEEKDAY(A2,2)
Fig. 2 Selecting the correct option
And now, Saturday is 6 and Sunday is 7. We can clearly see that weekends are numbers bigger that 5 (Fig. 3).
Fig. 3 Weekend bigger that 5
Now, we can simply create a logical test that defines if the week day number is greater than 5 (Fig. 4).
=WEEKDAY(A2,2)>5
Fig. 4 A simple logical test
When we copy our formula down, we can see that we have TRUE for Saturday and Sunday, and FALSE for the rest of the days (Fig 5).
Fig. 5 TRUE and FALSE
The results are proper, but we have to remember that we want to highlight the whole row. We have to ask ourselves how our reference should behave. We know that we will be copying the formula down and to the right (horizontally). What we have to remember is that we always want to look at column A, which is the Date column. Even if we want to go one or two columns to the right, we always want to look at the cell from column A (Fig. 6).
Fig. 6 A cell from column A
That’s why, we have to lock the column by pressing the F4 key, but not the rows. It means that we have to put only one dollar sign (Fig. 7).
=WEEKDAY($A2,2)>5
Fig. 7 Locking the column
When we copy the formula down, nothing changes. But, when we copy it to the right and down, we can see that TRUEs and FALSEs are in a row (Fig. 8).
Fig. 8 Rows
Now, we have to copy our formula and the select the range on which we want to add conditional formatting. The range starts with cell A2, so let’s press this cell, then Shift + →, two times to the right and Ctrl + Shift + ↓ to the end of our data. Cell A2 has to stay active and we will start creating our formula from the perspective of this cell (Fig. 10).
Fig. 9 Selected range with an active cell
Now, we go to the Home tab, then to Conditional Formatting, and we select the New Rule option (Fig. 10).
Fig. 10 New Rule option
In the window that will appear, we have to select the Use a formula to determine which cells to format bar, then paste our formula into the Edit the Rule Description and press the Format button (Fig. 11).
Fig. 11 New Formatting Rule window
In the Format Cells window, we press the Fill button, then choose the color we like. Let’s choose a shade of grey, then the OK button, and the OK button once again in the New Format Rule window (Fig. 12).
Fig. 12 Format Cells window
Now, we can see that each day of the weekend is highlighted the way we wanted (Fig. 13).
Sometimes, we want to count a certain number of weekdays between two dates. If we want to count workdays, we need to use the NETWORKDAYS function. But, if we want to count the number of, let’s say Fridays, we have to use the NETWORKDAYS.INTL function. We start with the start date, finish with the end day, and then we modify weekends. In the Excel help description, we can see that we can change working days and days off using a text string of 1s and 0s, where 1 means a day off and 0 means a working day. So, Monday, Tuesday, Wednesday, Thursday are 1, Friday is 0, Saturday and Sunday are 1 (Fig. 1).
Number of Fridays between two dates
=NETWORKDAYS.INTL(A2,B2,“1111011”)
Fig. 1 NETWORKDAYS.INTL function
Here, we have the number of Fridays between two dates. It’s important that the function also considers the start date and the end date in its calculations (Fig. 2).
Fig. 2 Number of Fridays
The NETWORKDAYS.INTL function can also work with holidays. Now, only Friday holidays are important 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)
Fig. 3 Holidays on Friday
And we have our results (Fig. 4).
Fig. 4 Results
Summing up, 0 means a working day, and 1 means a day off in the seven-number text string. If we write 0 also in the fourth place, it means that Thursday and Friday are working days (Fig. 5).
=NETWORKDAYS.INTL(A2,B2,1110011”,$F$3:$F#4)
Fig. 5 Two working days
As we can see, this small change modified our results once again (Fig. 6).
