If formula result = 1 then change following text from "Days" to "Day"

[Ironman]

Hi

My original post below concerned the calculation. The result precedes the words "Days Since Last Run" as in the formula below.

Original post from earlier

=TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>") &" Days Since Last Run"

I know it's pedantic but as I'm a pedant it doesn't look right grammatically when the result is 1 or if I ran today

Could the formula be amended or is there some code that could be used that adds either 'Day Since Last Run' in cell A9 when the result of the above formula is 1, "Days Since Last Run" when it is more than 1 or "You Ran Today" if it is 0 i.e. today?

Many thanks!

 

[Ironman]

Hi Peter, thank you so much for your help!

Here's what I input:

=SWITCH(TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>"),0,"Last exercise today",1,"Last exercise yesterday","Last exercise " &TEXT(TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>"),"d/mm/yyy"))

The result was

Last exercise 03/01/1900

I've checked the date range in the sheet and it's correct so I can't see anything obvious in that respect that I've done incorrectly, although as I'm now looking to include any exercise in the calc then I'm aware that OTHER should now be excluded from the MAXIFS conditions (it's only REST that I need to be included) but I tried that before I posted and it didn't work.

 

[Peter_SSs]

My mistake - guessing without test data. Still a bit the same but try

=SWITCH(TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>"),0,"Last exercise today",1,"Last exercise yesterday","Last exercise " &TEXT(MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>"),"d/mm/yyy"))

 

[Ironman]

I've just tested that and it came back

Last exercise 05/08/2021

Whereas it should be 06/08/2021

Fri, 6 Aug 2021	OTHER
Sat, 7 Aug 2021	REST

As I mentioned, the original solution ignored OTHER but this time I need to include it - I guess it's because OTHER is still being (incorrectly) excluded in the formula?

 

[Ironman]

Ahh, I've sorted it, as below:

SWITCH(TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>"),0,"Last Exercise Today",1,"Last Exercise Yesterday","Last Exercise " &TEXT(MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>"),"dd/mm/yyy"))

Thanks ever so much for all your help once again Peter!

 

[Peter_SSs]

Ahh, I've sorted it, as below:

Good news.

 

[Dave Patton]

A possible alternative.
I have not reviewed all the posts; you can edit the wording with this approach for your requirements.

Switch.xlsm
	A	B	C	D	E	F
1					0	Exercised Today
2					1	Exercised Yesterday
3					2	Exercised days ago =
4			Exercised days ago =5
5
1d

Cell Formulas
Range	Formula
C4	C4	=LET(num,TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>"),LOOKUP(num,E1:F3)&IF(num>1,num,""))

 

[Peter_SSs]

A possible alternative.

Apart from the request being to include the date of the last exercise, not the number of days ago, the OP has Excel 2019 (according to profile at least) so would not have the LET function so the formula would have to be expanded out in full.

 

[Ironman]

Hi again

Is there a way to fill the result cell the same colour as the last activity?

In other words, in the table in Post #1 the result cell fill colour would be the most recent exercise cell on 5 August and would match that shade of green (using Column A) and in the table in Post #13 the result cell would match the most recent cell for 6 August, and would be shaded the same light blue (again, using Column A).

Thanks again!

 

[Peter_SSs]

That is not possible using formulas. It would be possible with a macro, in which case you would probably scrap the formula altogether and get the macro to calculate the results as well as applying the formatting.

 

[Ironman]

OK Peter, many thanks.

I'll make a separate post.

Best regards

Paul