0

Good day everyone

For my coworkers, I am creating a Google Sheet for their follow-up files. However, I'm having an issue. There are multiple cells to look at to fill out details in another cell. I got the =IFS formula working, but it is not working properly. I'm probably missing out on something, but after multiple tries and staring at my screen for an hour, I'm not afraid to ask for some assistance.

The screenshot of what I'm working on: enter image description here

Column O is the cell I want to be filled out automatically based on columns H, I and J. So basically, if H is today, O should appear as "Status 1". If I is filled out as the date of tomorrow, it should be "Status 2", and so on.

The =IFS code I have now is

=IFS(H11>G11,"Status 1",I11>H11,"Status 2",J11>I11,"Status 3")

However, it keeps showing up as Status 1, even when the date of I is higher than H, and also when J is filled out, Status 1 keeps popping up and does not change to Status 3...

I can say, I'm quite lost.

I hope someone could help me out and that my information given is clear enough for a solution.

Thank you Anny

2
  • Apparently your formula (and that of @Blind's answer) doesn't refer to today's date or later dates ... is that really what you want to achieve or is it okay too?
    – Daniele
    Oct 1, 2022 at 1:16
  • @Daniele well noted but, based on the core issue as well as the purpose of the sheet, I expect it is largely unimportant vis-a-vis this question: Why isn't IFS working?. Anny is tracking an item through the stages of a repair process. Likely columns (H:Case Created, I:Diagnosis Completed, J:Quote Provided, M:Quote Accepted; N:Picked Up). reviewing your comment & the question exposes a flaw in the approach if the possibility exists that 2 stages are completed on the same day, and the date fields don't include time. I'll revise my answer to address.
    – Blindspots
    Oct 3, 2022 at 16:14

1 Answer 1

1

First Fulfilled Condition


The result in your example is correct:

=IFS(H11>G11,"Status 1",I11>H11,"Status 2",J11>I11,"Status 3")
=IFS( 2022-09-29 > 2022-01-01 ,I11>H11,"Status 2",J11>I11,"Status 3")
=IFS( 44833 > 44562,"Status 1",.,..)
="Status 1"

Why?
Because IFS stops after the first fulfilled condition.

If you want it to keep checking until it fails it would be :

=IF(H11>G11,
    IF(I11>H11,
       IF(J11>I11,"Status 3","Status 2"),"Status 1"),"")

Notes & Concerns

Assumptions

  • Your sheet's purpose is to track an internal repair process
  • Each column represents a different stage in the process.
  • Entering a date in a column indicates both that the stage was completed, as well as the date on which it was completed.
  • key stages in your example are somethin akin to:
Col  Label
------------------
H    File Created
I    Diagnosis Completed
J    Quote Provided (to client)
M    Quote Accepted (by client)
N    Item Picked Up (by client)

Concerns

  1. Can one or more stages be omitted? If so this compromises the current approach. e.g. Is there a quotation date if no fault is found during diagnosis?
  2. Can more than one stage be completed on the same day? If so, in the absence of including the time with the date, this compromises the current approach. e.g. Quote generated in the morning and accepted same day.
  3. There are references to things like 'today', 'tomorrow', but nothing in the approach indicates that this is being tested/enforced.

Alternate Approach

I think the following approach matches the intended logic better, doesn't care about duplicates or gaps, scales more easily, and is more easy to follow when editing.

Assumptions I made:

  • Status #'s are numbered consecutively from 1 to x where x = total number of stages in the process
  • there is a 1:1 relationship between a status # and logical position of a stage in the repair process
  • In the case of two or more stages completed on the same date, we want to return the highest status #

Using your example Data

  1. Use MAX to determine the largest date in an array of date values
 =max($H2,$I2,$J2,$M2,$N2)
 =max(2022-09-29,2022-09-30,,,) 
 =max(44833,44834,,,)
 =44834
  1. Use XMATCH to find the position of value returned in #1 by our MAX function.
    Note: [search_mode]=[-1] specifies to search from the last entry to the first (not reorder) insuring that if more than one entry has the same date, the higher status is returned.
  =XMATCH(search_key, lookup_range, [match_mode], [search_mode])
  =XMATCH(44834,{$H2,$I2,$J2,$M2,$N2},,-1)
  =XMATCH(44834,{44833,44834,,,},,-1)
  =2
  1. Your use case status allows us to simply join the XMATCH value to a text string to get the status:
  ="Status "&"2"
  =Status 2
  1. So, putting it all together in cell O2:
   ="Status "&XMATCH(max($H2,$I2,$J2,$M2,$N2),{$H2,$I2,$J2,$M2,$N2},,-1)
   ="Status "&XMATCH(44834,{4833,44834,,,},,-1)
   ="Status "&"2"
   =Status 2
  • You can add/remove/reorder stages simply by adding/removing/reordering the column references in the arrays in your formula.
  • There isn't any date validation in the formula. I assume that any validation would not be applied there but rather on the cells themselves. That makes much more sense than doing it in this formula as it would get big very fast and it varies by stage. For example you could just enforce dates or you could be more sophisticated if the business logic demands it.

Sample Data Validation:

 Cell  Data Validation Formula
----------------------------
 G2    =DATEVALUE(G2)<=TODAY()) 
 // either today's date or a date in the past.

 J2    =AND(DATEVALUE(J2)>=MAX(G2,H2,I2), 
            DATEVALUE(J2)<=TODAY())
 //  Today or in the past, but also equal to or greater all previous stages
1
  • 1
    +1 for explaining why the OP's formula always gives "Status 1". IFS function - "Additional conditions and values if the first one is evaluated to be false."
    – Tedinoz
    Oct 3, 2022 at 1:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.