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

by | Dec 7, 2022 | Excel

Some­times, our data is com­bined into one string and we want to extract only a piece of infor­ma­tion, which will help us prop­er­ly under­stand our data. 

How to extract a date num­ber and income from text string with a fixed width

Let’s start with extract­ing the date. What is impor­tant in our exam­ple, is that each string of infor­ma­tion is of the same length, i.e. it has got a fixed width. 

Fig. 1 Data of the same string lengths

We can clear­ly see, that the date starts with the eighth sign and has got eight signs itself (mm/dd/yy). If we want to extract a cer­tain piece of infor­ma­tion, we have to use the MID func­tion. First, we have to write A2 in the argu­ment, because our infor­ma­tion is locat­ed 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 for­mu­la (Fig. 2).

=MID(A2,8,8)

Fig. 2 For­mu­la for extract­ing a date

After copy­ing it down, we have the results. It’s impor­tant though, that the MID func­tion, just like all text func­tions, returns results as text. We can see it because each date is aligned to the left. It may be prob­lem­at­ic if we want to do some date oper­a­tions. We can change the text by adding 0 to our for­mu­la. In oth­er words, we can per­form any math­e­mat­i­cal oper­a­tion that won’t change the returned date (Fig. 3). 

=MID(A2,8,8)+0

Fig. 3 Adding 0 to dates

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

=MID(A2,1,6)

Fig. 4 MID func­tion for­mu­la for extract­ing Id numbers

As we can see, Excel extract­ed Id num­bers. Since it is text, Excel left the lead­ing zero for us. We can leave Id num­bers as text if it’s impor­tant 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

Fig. 5 Adding 0 to Id numbers

We can see that Id num­bers moved to the right and the lead­ing zero van­ished. The last piece of infor­ma­tion we want to extract is the Income. Income val­ues don’t have a fixed width. But, since it’s the last piece of infor­ma­tion, we can extract more signs than we need. Let’s extract also the dol­lar sign. The dol­lar sign is in the sev­en­teenth posi­tion. We start writ­ing the MID for­mu­la, then we add cell A2, then 17, and then we have to add the num­ber of places we want too extract. Let’s write 10. It’s, in fact, more that we need, how­ev­er it’s not less either. The MID func­tion will extract the infor­ma­tion to the end with­out any errors (Fig. 6). 

=MID(A2,17,10)

Fig. 6 MID func­tion for extract­ing Income

As in our pre­vi­ous for­mu­las, the results are also text. By adding zero, we change the results into numbers.

MID(A2,17,10)+0

Fig. 7 Text into numbers

We can see that now the results are num­bers. How­ev­er, we can see that we lost the dol­lar sign. This is the way Excel con­verts num­bers with dol­lar signs before them. Indeed, the dol­lar sign isn’t impor­tant in our data, so we should start from the eigh­teenth sign (Fig. 8).

Fig. 8 Remov­ing the dol­lar sign

Now, we have only num­bers. If we want to add a cur­ren­cy, we just go to the Home tab, drop down the Gen­er­al bar and select the Cur­ren­cy option (Fig. 9). 

Fig. 9 Adding a currency

When we have our results with zeros at the end, we can remove them by press­ing the Decrease Dec­i­mal option (Fig. 10).

Fig. 10 Remov­ing zeros at the end

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

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

Highlight weekends and holidays with conditional formatting

by | Dec 5, 2022 | Excel

Some­times, we want to high­light whole rows of hol­i­days and week­ends. In our case they will be the sec­ond, the third and the fourth of Sep­tem­ber, the sixth of Sep­tem­ber and so on, depend­ing on how many hol­i­days and week­ends we have. We can high­light them with con­di­tion­al for­mat­ting, but first of all, we have to cre­ate a prop­er for­mu­la. We are going to use the NETWORKDAYS func­tion. We want to start with the date from the first row and fin­ish with the same date. We also have to add hol­i­days and lock it (Fig. 1). 

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

If a giv­en day is a work­ing day, the func­tion returns 1, and if it’s a day off, it returns 0 (Fig. 2). 

Working days and days off
Fig. 2 Work­ing days and days off

Now, we want to high­light the rows, where we have 0. We can do it by cre­at­ing a prop­er log­i­cal test. We have to check whether a val­ue returned by the NETWORKSDAYS func­tion is equal to 0 (Fig. 3).

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

Fig. 3 A sim­ple log­i­cal test

Now, we want to ask our­selves how we want our ref­er­ence to behave. Since we want to copy the for­mu­la down, we want to change rows, as there are dif­fer­ent dates in each row. We also want to high­light a whole row, so we always want to look at the first cell in the row because it’s a date. Hav­ing this in mind, we have to lock the col­umn by insert­ing a sin­gle dol­lar sign before the name of the col­umn, but not before the row num­ber. We must do this both in the start date and the end date argu­ment (Fig. 4).

Locked cell
Fig. 4 Locked cell

After copy­ing it down and to the right, we have prop­er results. We have TRUE for week­ends and hol­i­days, and FALSE for work­ing days (Fig. 5). 

Proper results of TRUE and FALSE
Fig. 5 Prop­er results of TRUE and FALSE

Now, we can copy our for­mu­la in the edit mode and select the range in which we want to have con­di­tion­al for­mat­ting. 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 Con­di­tion­al For­mat­ting, then the New Rule option (Fig. 6). 

New Rule option
Fig. 6 New Rule option

In the New Rule For­mat­ting win­dow, we have to select the Use a For­mu­la to deter­mine which cells to for­mat bar, then paste the for­mu­la into the Edit the Rule Descrip­tion bar, and click on the For­mat tab (Fig. 7). 

New Formatting Rule window
Fig. 7 New For­mat­ting Rule window

In the For­mat Cells win­dow, we select the Fill tab, then choose a nice col­or, then press the OK but­ton in the win­dow, then the OK but­ton in the sec­ond win­dow (Fig. 8).

Format cells window
Fig. 8 For­mat cells window

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

Highlighted rows
Fig. 9 High­light­ed rows

Even if we mod­i­fy the hol­i­days, which are our cri­te­ria, our high­light­ed rows also change (Fig. 10). 

Criteria modification
Fig. 10 Cri­te­ria modification

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

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