There seem to be several of these, but thought it would be good to start one that is clearly a general one that isn’t about a specific query.

Request help or post success stories below!

There seem to be several of these, but thought it would be good to start one that is clearly a general one that isn’t about a specific query.

Request help or post success stories below!

2 Likes

I would have the date you want in a separate cell, where the formula you use is:

="<"&TODAY()-90

@still_here and anyone else

Would anyone be kind enough to explain what the quotation marks, and sign, and brackets around nothing are doing in this formula?

1 Like

Quote marks are making it text that’ll be excluded from the formula but added to the result.

The & sign connects it.

The () is there to denote a function.

1 Like

I think the way excel works is that you have to have brackets after a function, but the today function doesn’t actually need you to put anything in between the brackets, hence they’re just empty.

Using quotations allows you to put text in a formula, so the “<” is just adding a less than sign before today’s date, then the & sign means it is to be included in additino to the value produced by the other functions in the formula. I also use this often when I have one cell for forename and one for surname and I want to combine them - for example

=A2 & " " & B2

In the above formula I’m adding a space in between the value of cell a2 and cell b2

2 Likes

So, in English, your formula says

This is a formula! But the < isn’t part of it but needs to be there. Oh, and we need to use today’s date from the excel function, and take away 90 from it.

= - This is a formula!

" " - but the < isn’t part of it but needs to be there

& - oh, and

TODAY() - we need to use today’s date from the excel function

-90 - take 90 away from it.

3 Likes

I don’t understand why < isn’t part of the formula here and is treated as text

Must be the wish to display the “less than” in the result in the cell.

2 Likes

I like answering excel formula questions; will follow this thread.

2 Likes

There might be a way around it, but when I did a little test and tried to include the less than sign as part of the countifs formula excel wasn’t having it. But if you have it in a separate cell you can refer to it, and the formula will understand it. Don’t really know why.

1 Like

I think it’s hard to judge without the context of the rest of it. All we’re saying is"Less than 90 days in the past" which feels an odd sort of comparison

You need to format the whole second clause as a string if you want it to evaluate something more complex than a single value. Using a cell reference just brings the whole condition as a string rather than you having to build if up yourself.

These will all give you the same result

=COUNTIF($A$1:$A$10, “<6”)

=COUNTIF($A$1:$A$10, “<” & “6”)

=COUNTIF($A$1:$A$10, “<” & 6) ← Here the & will implictly convert the 6 to a string

=COUNTIF($A$1:$A$10, C1) ← Where C1 has the value “<6”

=COUNTIF($A$1:$A$10, “<” & C2) ← Where C2 has the value 6

1 Like

Ah cool, makes sense

Not really. You’re looking for rows which haven’t been updated in the last three months. Presumably.

1 Like

Yeah but why is it not the actual formula? Why is this a text version so you have to do it by eye? And you’d be comparing two dates not formatted as dates

Covered a little bit above, but to try and clarify.

- It’s feeding into another formula which parses the text and evaluates it against the range with the values.
- It doesn’t need to be done like that but it can be useful to split out the components to understand how it works. To be honest, the 90 is the part I’d have put in a separate cell, then you can much more easily change to check 60 or 120 days just by modifying one cell.
- Excel is actually pretty good at interpreting values and handling formatting like this (much more so than gSheet.) For example, if you have a column of dates entered in a standard format these two formulas will give you exactly the same result:

=COUNTIF($L$3:$L$13,"<23/03/21") ← Date passed as a string in your local standard format

=COUNTIF($L$3:$L$13,"<44278") ← Date passed as integer value based off whatever MS base date is.

1 Like

Ha sorry, this spun out a bit.

I hadn’t seen Still here’s replyto harru when I posted (poss we posted at the same time) so my answer to Harru was just literal, as in it makes no sense because it’s devoid of context.

I trusted there was context I just didn’t know it.

This stuff about countifs sounds totally batshit though. How very very excel

Think I need to brush up on my strings

In my understanding a string is any text with no numerical or formulaic value, so eg today() is different to “today()”

You probably needed to do a bit of pre-reading first

1 Like

That’s pretty much it and I’m probably needlessly complicating things here. The main things for this CountIf() stuff are that you need to convert your condition logic to a string before you pass it to the formula and that internally the formula will try to convert it back to a value which it can compare against each row.

Actually, another way to think of this is, how would you compare a single row?

= IF( $B2 **< Today()**, 1, 0)

which becomes

= IF( $B2 **<25/03/21**, 1, 0)

So, for a CountIf you want to pass that *whole comparison logic including the < operator* as text.

If you try to send that first version it’s going to look for a string which is greater in value than “today()”, which isn’t any use. Therefore, you first need to calculate the result of the Today() function and then concatenate it with the **<** to get a valid string

Wow. I fucking love Excel chat.

Just think of it like any other overloaded function.

2 Likes

Mate.

I understand what an overloaded function is ffs😂

I meant what still here had said about it only recognising the angle bracket symbol if you’ve printed it to the cell and then reference that.

I was about to go to bed so I didn’t read your big countifs thing sorry

1 Like