[administrator@dailyexcel.net]

In principle, you can’t, as it is stored as text. If you want to use it as an operator, you will have to resort to something like this:

=IFS(
A1=”>”;B1>C1;
A1=”>=”;B1>=C1;
A1=”=”;B1=C1;
A1=”<=”;B1<=C1;
A1=”<“;B1<C1)

This formula checks if a value in B1 is whatever is defined in A1 then a value in C1.

[administrator@dailyexcel.net]

The MATCH function:

older (compatibility advantage) and simpler syntax

The XMATCH function:

extra features such as last-to-first search, much better with next smaller (larger) matching

As far as I know, there are no noticeable differences in performance.

[administrator@dailyexcel.net]

Hm, you could duplicate your Date table, filter out dates that are not end-of-month dates, and then join that table.

Br,

[administrator@dailyexcel.net]

You will not be able to accomplish what you want by unpivoting columns. However, you can:

Go to your Date table, add Custom Index Column, Starting Index 0, Increment 0

Go back to your Query, add Custom Index Column, Starting Index 0, Increment 0

Merge Date table into your Query using those two new columns; use Left Outer join

After expanding, you will have all of the rows from your Date table available.

Add Conditional Column, “Custom”, each if ([Joined]›=[From] and [Joined] ‹= [To]) then “keep” else “delete”

Filter Custom and keep only “keep”

Delete all the unnecessary columns.

Br,

[administrator@dailyexcel.net]

That’s a tilde ~ character.

To replace a tilde (or a wildcard character, a question mark ? and asterisk *) you will have to use another tilde to tell Excel you aren’t trying to use wildcard characters in Find and Replace.

Type

~~ in order to replace ~

~? in order to replace ?

~~ in order to replace ~

Br,

• This reply was modified 1 month, 3 weeks ago by administrator@dailyexcel.net.

[administrator@dailyexcel.net]

Not 100% sure I understand what is going on here.

If your INDEX has more than one row and column, you will need INDEX MATCH MATCH instead of INDEX MATCH.

If you are trying to retrieve a column and compare it with your FV6:FV34 column, you can try something like this:

=FILTER(GE6:GE34,FV6:FV34=INDEX(FT6:GA34,0,MATCH("UGW2800",FT6:GA6)))

If you want, you can email your example.

Br,

[administrator@dailyexcel.net]

File/Options/Customize Ribbon

You can remove any unwanted commands, including those added by third-party software.

Update: I’ve expanded on this topic in the following article.

Br,

• This reply was modified 2 months ago by administrator@dailyexcel.net.

[administrator@dailyexcel.net]

Excel does not limit the number of worksheets!

For example, you can create 1000 worksheets using the following VBA code:

Sub AddSheets()

Dim n As Integer

For n = 2 To 1000

Sheets.Add(After:=Sheets("Sheet" & n - 1 )).Name = "Sheet" & n

Next n

End Sub

This number probably comes from the limits imposed by other (legacy) software, i.e. Lotus, OpenOffice etc.

[administrator@dailyexcel.net]

Link to the other topic.

[administrator@dailyexcel.net]

If it helps, you can use the SUBSTITUE function to clean up your text string before giving it to the DATEVALUE function, something like this:

=DATEVALUE(SUBSTITUTE("dd.mm.yyyy.";".";"";3))

Br,

• This reply was modified 2 months ago by administrator@dailyexcel.net.

[administrator@dailyexcel.net]

Hi John,

I also remember using the DATEVALUE function in that way in pre-Excel 2021 versions; there is even an example given in this blog post, and it also does not work for me in the Excel 2021 or Microsoft 365 version.

On the other hand, text strings such as “dd-mm-yyyy” are still accepted by the function!

Br,

[Author]

Posts