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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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

Excel Formula:
=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!
 
Last edited:
Hi Peter, thank you so much for your help!

Here's what I input:

Excel Formula:
=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.
 
Last edited:
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
My mistake - guessing without test data. Still a bit the same but try

Excel Formula:
=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"))
 
Upvote 0
I've just tested that and it came back
Last exercise 05/08/2021

Whereas it should be 06/08/2021
Fri, 6 Aug 2021OTHER
Sat, 7 Aug 2021REST


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?
 
Last edited:
Upvote 0
Ahh, I've sorted it, as below:
Excel Formula:
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!
 
Upvote 0
A possible alternative.
I have not reviewed all the posts; you can edit the wording with this approach for your requirements.

Switch.xlsm
ABCDEF
10Exercised Today
21Exercised Yesterday
32Exercised days ago =
4Exercised days ago =5
5
1d
Cell Formulas
RangeFormula
C4C4=LET(num,TODAY()-MAXIFS(A12:A20000,B12:B20000,"<>REST",B12:B20000,"<>"),LOOKUP(num,E1:F3)&IF(num>1,num,""))
 
Upvote 0
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.
 
Upvote 0
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!
 
Last edited:
Upvote 0
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.
 
Upvote 0
OK Peter, many thanks.

I'll make a separate post.

Best regards

Paul
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top