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!

 

[kweaver]

Ugly, but should do it:

=TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>") &" Day" & IF(1=abs(TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>")),"","s")&" Since Last Run"

 

[Ironman]

Hey that works great kweaver, many thanks!

Is the '0' value condition ("You Ran Today") possible by amending the above formula or would that require code?

Thanks again!

 

[kweaver]

=IF(0=TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>"),"You Ran Today",TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>") &" Day" & IF(1=ABS(TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>OTHER",B12:B20000,"<>")),"","s")&" Since Last Run")

 

[Habtest]

Not elegant as well, but try

=SWITCH(TODAY()-MAXIFS(...),0,"You Ran Today",1,"1 Day Since Last Run",TODAY()-MAXIFS(...)&" Days Since Last Run")

 

[kweaver]

@Habtest: good use of switch. I keep forgetting about that function.

 

[Ironman]

kweaver and Habtest thank you both so much, they both work equally well but I can only select 1 solution. I'm amazed at the power of formulas.

 

[Habtest]

@Ironman Glad we could help and thanks for the feedback!

@kweaver

 

[Ironman]

I'm just reviewing Habtest's formula

=SWITCH(TODAY()-MAXIFS(...),0,"You Ran Today",1,"1 Day Since Last Run",TODAY()-MAXIFS(...)&" Days Since Last Run"

I need to amend this for another cell, to return the number of dates since the last REST date with "Last exercise today" if 0, "Last exercise yesterday" if 1 and "Last exercise [and actual date of exercise]" if >1. I've tried to amend it myself but have failed miserably

Would you be able to resolve this please?

Many thanks again!

 

[Peter_SSs]

Something like this?

=SWITCH(TODAY()-MAXIFS(...),0,"Last exercise today",1,"Last exercise yesterday","Last exercise " &TEXT(TODAY()-MAXIFS(...),"d/mm/yyy"))