BLOG - Excel And Adam

How to extract a date number and income from text string with a fixed width

by Excel and Adam | Dec 7, 2022 | Excel

Sometimes, our data is combined into one string and we want to extract only a piece of information, which will help us properly understand our data.

How to extract a date number and income from text string with a fixed width

Let’s start with extracting the date. What is important in our example, is that each string of information is of the same length, i.e. it has got a fixed width.

We can clearly see, that the date starts with the eighth sign and has got eight signs itself (mm/dd/yy). If we want to extract a certain piece of information, we have to use the MID function. First, we have to write A2 in the argument, because our information is located in this cell. Then, we write 8 because we start with the eighth sign, and then 8 again because we want to extract eight chars. And we have our formula (Fig. 2).

=MID(A2,8,8)

After copying it down, we have the results. It’s important though, that the MID function, just like all text functions, returns results as text. We can see it because each date is aligned to the left. It may be problematic if we want to do some date operations. We can change the text by adding 0 to our formula. In other words, we can perform any mathematical operation that won’t change the returned date (Fig. 3).

=MID(A2,8,8)+0

Now, we can see that the values are aligned to the right. It means that Excel treats them like numbers, i.e. like dates. We can extract Id numbers the same way. They start with the first sign and have got 6 digits in total. We can also use the MID function. First, we select cell A2, then write 1 because it starts from the first sign, then we write 6 because we extract 6 digits (Fig. 4).

=MID(A2,1,6)

Fig. 4 MID function formula for extracting Id numbers

As we can see, Excel extracted Id numbers. Since it is text, Excel left the leading zero for us. We can leave Id numbers as text if it’s important for us. If it isn’t, we can do the same as with dates, i.e. add zero (Fig. 5).

=MID(A2,1,6)+0

We can see that Id numbers moved to the right and the leading zero vanished. The last piece of information we want to extract is the Income. Income values don’t have a fixed width. But, since it’s the last piece of information, we can extract more signs than we need. Let’s extract also the dollar sign. The dollar sign is in the seventeenth position. We start writing the MID formula, then we add cell A2, then 17, and then we have to add the number of places we want too extract. Let’s write 10. It’s, in fact, more that we need, however it’s not less either. The MID function will extract the information to the end without any errors (Fig. 6).

=MID(A2,17,10)

Fig. 6 MID function for extracting Income

As in our previous formulas, the results are also text. By adding zero, we change the results into numbers.

MID(A2,17,10)+0

We can see that now the results are numbers. However, we can see that we lost the dollar sign. This is the way Excel converts numbers with dollar signs before them. Indeed, the dollar sign isn’t important in our data, so we should start from the eighteenth sign (Fig. 8).

Now, we have only numbers. If we want to add a currency, we just go to the Home tab, drop down the General bar and select the Currency option (Fig. 9).

When we have our results with zeros at the end, we can remove them by pressing the Decrease Decimal option (Fig. 10).

https://www.youtube.com/watch?v=O39qM233-68

Number of Fridays between two dates

by Excel and Adam | Dec 6, 2022 | Excel

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”)

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).

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)

And we have our results (Fig. 4).

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)

As we can see, this small change modified our results once again (Fig. 6).

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

Highlight weekends and holidays with conditional formatting

by Excel and Adam | Dec 5, 2022 | Excel

Sometimes, we want to highlight whole rows of holidays and weekends. In our case they will be the second, the third and the fourth of September, the sixth of September and so on, depending on how many holidays and weekends we have. We can highlight them with conditional formatting, but first of all, we have to create a proper formula. We are going to use the NETWORKDAYS function. We want to start with the date from the first row and finish with the same date. We also have to add holidays and lock it (Fig. 1).

=NETWORKDAYS(A2,A2,$G$2:$G$3)

If a given day is a working day, the function returns 1, and if it’s a day off, it returns 0 (Fig. 2).

Now, we want to highlight the rows, where we have 0. We can do it by creating a proper logical test. We have to check whether a value returned by the NETWORKSDAYS function is equal to 0 (Fig. 3).

=NETWORKDAYS(A2,A2,$G$2:$G$3)=0

Now, we want to ask ourselves how we want our reference to behave. Since we want to copy the formula down, we want to change rows, as there are different dates in each row. We also want to highlight a whole row, so we always want to look at the first cell in the row because it’s a date. Having this in mind, we have to lock the column by inserting a single dollar sign before the name of the column, but not before the row number. We must do this both in the start date and the end date argument (Fig. 4).

After copying it down and to the right, we have proper results. We have TRUE for weekends and holidays, and FALSE for working days (Fig. 5).

Now, we can copy our formula in the edit mode and select the range in which we want to have conditional formatting. We click on the Shift + →, two times to the right, and Ctrl + Shift + ↓ to select the data to the end. While cell A2 is still active, we can go to Home tab, then Conditional Formatting, then the New Rule option (Fig. 6).

In the New Rule Formatting window, we have to select the Use a Formula to determine which cells to format bar, then paste the formula into the Edit the Rule Description bar, and click on the Format tab (Fig. 7).

In the Format Cells window, we select the Fill tab, then choose a nice color, then press the OK button in the window, then the OK button in the second window (Fig. 8).

And there we have it. We can see that each row with a day off is highlighted in green (Fig. 9)

Even if we modify the holidays, which are our criteria, our highlighted rows also change (Fig. 10).

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

Date after X work days

by Excel and Adam | Dec 2, 2022 | Excel

If you want to find a date after a certain number of workdays, you can use the WORKDAY function. You just need the start date and the number of working days (Fig. 1).

Date after X work days

We can see that Excel shows the date after the corresponding number of working days. You have to remember that the WORKDAY function doesn’t consider the start date in its calculations. It means that if you have only one workday, you will go to the next working day (Fig. 2).

In the WORKDAY function, you can even add holidays by just selecting a range with proper dates. Remember to lock it (F4) (Fig. 3).

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

The WORKDAY function considers the weekend as Saturday and Sunday, which is correct in most of the countries, however we can modify it. When we want only Sunday to be our weekend, we have to choose the WEEKDAYS.INTL function. The function consists of the start date and the number of working days. The third argument is the weekend, where we can change the weekend days. Let’s choose Sunday (Fig. 5).

Then, we are choosing holidays by selecting a proper range and locking it (F4 key). Our formula is ready (Fig. 6).

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

We can see that our results differ from the the previous ones because now Saturday is our working day (Fig. 7).

Number Of Working Days Between Two Dates

by Excel and Adam | Dec 1, 2022 | Excel

When we want to count the number of working days between two dates we can use the NETWORKDAYS function. The function is very simple, as it needs only the start and end dates. And that’s it (Fig. 1).

Number Of Working Days Between Two Dates

=NETWORKDAYS(A2,B2)

And we have the numbers of working days between two dates. It’s important that the NETWORKDAYS function considers also the start and end days. If the start and end days are the same, the number will tell us whether the day is a working day or not (Fig. 2).

We can also add holidays to this function in the third argument. We just have to select a range with proper dates and press the F4 key to lock it (Fig. 3).

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

Now, we can see that the number of days changed due to those holidays (Fig. 4).

In the NETWORKDAYS function, the weekend is considered as Saturday and Sunday. However, we can modify it by using the NETWORKDAYS.INTL function. The syntax of those two functions is almost the same. Apart from the start and end day, we can choose what our weekend days will be. Let’s choose Sunday only (Fig. 5).

Then, we can add holidays if we want. Let’s select the proper holiday range and press the F4 key to lock it (Fig. 6).

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

And we have our results. We can see that the number of working days between the dates grew because Saturday is a working day (Fig. 7).

How to group columns and rows

by Excel and Adam | Nov 30, 2022 | Excel

Today, we are going to learn how to group columns and rows. In our example, we have month by month sales, and their summaries by the first quarter, the second quarter and half a year with the use of the SUBTOTAL function.

How to group columns and rows

Sometimes, we don’t want to show lower values, i.e. values from individual months. We can do it by hiding those columns (Fig. 2).

And sometimes, we want to show those values (Fig. 3).

Those tasks, however, can sometimes be a bit tedious. That’s why we are going learn how to group those columns to make it simpler. First, you have to select the columns you want, then go to the Data tab and click on the Group command. Now, we can see a line above column names, which allows us to hide the columns just by clicking on the minus sign (Fig. 4).

We can also unhide them by clicking on the plus sign (Fig. 5).

Now, the task is quite simple. You can hide or unhide many columns at once. We can group even more columns this way, however, it’s important to have a space of at least one column between those groups so that Excel doesn’t combine those groups in one (Fig. 6).

When we want to ungroup our data, there is a simple solution. You have to go to the Data tab, expand the Ungroup command, and choose the Clear Outline option (Fig. 7). This way, Excel will clear all groups in the sheet. Usually, it’s easier to clear all groups and start creating new groups from the beginning than trying to fix it.

Due to the fact that our values are combined from different columns, we can make Excel group the values for us. You just select any cell, go to the Data tab, expand the Group command and choose the Auto Outline option (Fig. 8).

This way, Excel has created three groups for us. Those groups aren’t on the same level because each lower level group combines three columns, and the upper level group combines all the columns from the lower groups and a few more (Fig. 9).

When we click on the group level number, Excel will expand or collapse all groups in this level (Fig. 10).

Now, let’s group some rows. We don’t have any function that sums up or combines rows, so we have to do it manually. We have to select the rows we want, then go to the Data tab, then the Group option (Fig. 11).

If we select a range, not whole columns or rows and press the Group command, Excel doesn’t know which ones we want to group. Then we have to select the proper radio button (Fig. 12).

We can also select more rows and group them by pressing the Group option to create a group on a higher level. This group will consist of groups from a lower level. We can expand and collapse them the using also level number buttons (Fig. 13).

Dynamic chart title, link chart title with a value from the cell

by Excel and Adam | Nov 29, 2022 | Excel

Today, we are going to make a simple trick, i.e. linking the Chart Title with a value from a cell. It will make the Chart Title a bit more dynamic. The task is simple. We have to select the chart, so that it has solid borders. We go to the Chart Title edit mode. As we can see, the Chart Title borders change into a dotted line, which we don’t want. We have to click something else on the chart, then click again on the chart title one time, so that the borders stay solid (Fig. 1).

Dynamic chart title, link chart title with a value from the cell

Now, we have to click the equal sign, which will appear in the formula bar (Fig. 2).

We are starting to write a formula for the Chart Title. We are clicking cell A1 because we want to have reference to this cell. Then, we are pressing Enter to link the Chart Title to this cell (Fig. 3).

Chart Title linked to a cell — Fig. 3 Chart Title linked to cell A1

When we change a value in the cell, the Chart Title also changes (Fig. 4).

This simple trick works also with Axis title. We have to select the Axis title (Fig. 5).

Then, write the equal sign and select a proper cell. In our case it’s A10. After entering it, the Axis title changes (Fig. 6).

We can do the same with the bottom Axis title (Fig. 7).

That’s how you connect the Chart title and Axis titles to values from cells.

How to extract last row

by Excel and Adam | Nov 28, 2022 | Power Query

Today, we want to extract the last row with given currencies. Our data is sorted randomly. If we want to consider the date column, we will have to sort it first. We can do it manually, by going to the end of date and find the last row. However, we want to sort it in Power Query, which is more automatic. We just have to select one cell from our table, go to Data tab, and click on the From Table/Range command (Fig. 1).

How to extract last row

This command exports our data to Power Query. We can see, that Power Query considers our data as a date and time. In our example, it isn’t important so let’s leave it like that. If we want to sort our dates, we just click on the A to Z sorting command on the Home tab (Fig. 2).

Now, the last row is the newest date (Fig. 3).

Since we are interested in currencies, we want to group our data by the Currency column. We have to click one cell from the Currency columns and go to the Group By command in the Home tab. In the Group By window that has appeared, we can see that the Currency bar is selected. In the Operation bar, we need to select All Rows and, in the New column name, let’s write temporary. Then we are pressing the OK button (Fig. 4).

And we have our data grouped by currencies (Fig. 5).

There is a table for each currency that contains all rows with a given currency. We have sorted the dates earlier, so in the tables, the last date is also the newest exchange rate. And the newest exchange rate is what we want (Fig. 6).

Let’s go to the Add Column tab, then to the Custom Column command. A window will appear, where we write the name. Let’s call it Last. Then, we need to write a formula in the Custom column formula box. We are writing Table.LastN command to attract the last row of the table. The function we are writing needs a table, so we are choosing our table called temporary, then we need to write the number of rows from the end. We want just one row, so let’s write it, then close the formula. This is the whole formula we need, so let’s press the OK button (Fig. 7).

=Table.LastN(temporary),1)

We can see that we have the Last column. In the column, there are tables, but each table contains only one row for each currency (Fig. 8).

We don’t need the temporary column, so let’s delete it (Fig. 9).

Now, we can expand the Last column by pressing the icon shown in Fig. 10. In the window that has appeared, we deselect the Currency and the Use original column name as prefix checkboxes. Then, we press the OK button (Fig. 10).

Now, we can see a proper table with currency, and the newest date with the exchange rate. Let’s go to the Home tab, and Close&Load to command (Fig. 11).

We moved back to Excel, where we have to select the Existing worksheet radio button, then click on cell F1 and deselect the Add this data to the Data Model checkbox, and press the OK button (Fig. 12).

And we have our results. However, we have to remember that Power Query not always extracts proper data formatting. That’s why we have to go to the Home tab and select proper data formatting (Fig. 13).

Let’s make a test and change some some dates in different currencies, then refresh our table (Fig. 14).

We can see that the Query is automatic when we refresh it (Fig. 15).

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

First day of the next month

by Excel and Adam | Nov 25, 2022 | Excel

Sometimes, we want to get the first day of the next month. I will show you two solutions how to calculate this date in Excel. The first solution uses the DATE function. It needs the number of days, months and years. First, we will write the number of years, which we can extracted from the current date using the YEAR function (Fig. 1).

First day of the next month

=DATE(YEAR(A2

The same case is with the month number. We are using the MONTH function and extracting the number from the date. However, we have to remember that we want to have the next month. That’s why we are adding +1. It’s important that the DATE function will work with numbers larger than 12. It will just go to the next year, or the previous one if the number is lower than zero. Then, we are putting 1 because we want the first day of the month. And just like that, we have the whole formula (Fig. 2).

=DATE(YEAR(A2),MONTH(A2)+1,1)

After entering and copying it down, we have the results (Fig. 3).

If we want to go further into the future, we just have to add more months, let’s say 3 (Fig. 4).

=DATE(YEAR(A2),MONTH(A2)+3,1)

We can see that the function goes to next years in certain dates (Fig. 5).

If we want to go back in time, we have to subtract a number, let’s write ‑3 (Fig. 6).

=DATE(YEAR(A2),MONTH(A2)-3,1)

And we are in the past. As we can see, the number of years is also calculated analogically (Fig. 7).

The second solution uses the EOMONTH function. We have our starting day and the number of months we are moving. Since we want to have the fist day of the next month, first, we need to go to the last day of the current month (Fig. 8).

=EOMONTH(A2,0)

And we have the last day (Fig. 9).

However, we want the day after, so we have to add 1 (Fig. 10).

=EOMONTH(A2,0)+1

Now, we have the first days of the next months (Fig. 11).

Fig. 11 The first day of the next months

When we want to have the first day of the month that is three months into the future, we just have to add 2 months in the second argument of the EOMONTH function. It’s because we will go 2 months into the future, and then we have +1 at the end, so it sums up to 3 months (Fig. 12).

=EOMONTH(A2,2)+1

And we have proper dates (Fig. 13).

Analogically, when we want to go back in time, we need to write one month more than we really want as a negative number because we are going to the last day of the month and then we are adding 1, so in total we are going back 3 months (Fig. 14).

=EOMONTH(A2,-4)+1

And we have the solution (Fig. 15).

The results are the same, so it’s up to you which solution you will choose.

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

Last day of a month, EOMONTH function

by Excel and Adam | Nov 23, 2022 | Excel

Sometimes, we need to know what the last day of the moth is. In fact, it’s really simple to find it out because we have the end of month function — EOMONTH. This function needs a starting day and the number of moths we are moving. When we want the end of the current month, we just put 0 in the second argument of the function. Then, we must close the formula and that’s it (Fig. 1).

Last day of a month, EOMONTH function

=EOMONTH(A2,0)

OEMONTH function with 1 — Fig. 1 OEMONTH function with 0

We have the last days of current months. The EOMONTH function shows the last day even if the starting day is the last day of the month (Fig. 2).

The EOMONTH function can move to the future. When we want to go one month into the future, we just put positive number (for example 1) instead of 0 (Fig.3).

=OEMONTH(A2,1)