0

I have a worksheet that calculates "begin date" (Column E) and "end date" (Column F) of an activity, then compares it to the "Ideal no. of days" (Cell H1) to decide if the process faced a delay.

Examples:

Activity 1 - Took 13 days, which 6 days more than ideal, and is therefore LATE.

Activity 2 - Took 4 days, which is less than ideal period, and therefore is NOT LATE.

How can I get both results in just one cell?

Ideal state would be:

Activity 1 output - LATE (-6)

Activity 2 output - NOT LATE (3) / ON-TIME

How it looks on google sheets

Edit: Added formula currently in use. It can also be seen on the screenshot.

=IF(G4>7, "LATE", "NOT LATE")

Improve this question
7
  • 1
    Welcome to Stack Overflow. You might get a better response to your question if you post your formula, the current result, and the desired result as text in the body of your question. Commented Apr 8, 2021 at 13:23
  • Thanks! I have edited the post to add the formula I am currently using. My objective is to merge columns H & I into one. Commented Apr 8, 2021 at 14:05
  • Formula in H4: =IF(I4<0,CONCAT("LATE (",I4,")"),CONCAT("NOT LATE (",I4,") / ON-TIME")) Commented Apr 8, 2021 at 14:11
  • Thank you. Which cell did you put the formula in? I tried with Cell J, but it returned an error - Wrong number of arguments to CONCAT. Expected 2, received 3. Commented Apr 8, 2021 at 14:18
  • Here's a link to the sheet: docs.google.com/spreadsheets/d/… Commented Apr 8, 2021 at 14:19

3 Answers 3

Reset to default
1

I added a new sheet ("Erik Help"). In that sheet, I removed all of your individual formulas from G4:H and replaced them with one array formula in G3 and one array formula in H3. Each of these formulas will produce the header and all column results for their respective ranges.

The G3 formula:

=ArrayFormula({"Time Taken";IF(NOT(ISNUMBER(E4:E))+NOT(ISNUMBER(F4:F)),,F4:F-E4:E)})

The H3 formula:

=ArrayFormula({"Delay Status";IF(NOT(ISNUMBER(F4:F)),IF(NOT(ISNUMBER(E4:E)),,"IN PROGRESS"),IFS(F4:F-E4:E>7,"LATE (",TRUE,"ON TIME (")&H1-(F4:F-E4:E)&")")})

These formulas may seem "long" to some. They're really not, but they are longer than one might expect because they control for several likely error scenarios.

Here, I used the ampersand symbol for concatenation of the final "phrases" in H4:H.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, Erik. This helped solve the problem and I have managed to use it successfully. Have a great weekend!
Glad to hear it. Would you take a moment and mark my post as "Best Answer"? This allows contributors to see from the main listings page which issues are already fully resolved; and it helps future site visitors like yourself quickly find the answers most relevant to their own searches and needs. Thanks.
0

Formula in Cell H8:

IF($G8>$H$1, CONCATENATE("LATE ", SUM($G8-$H$1)), CONCATENATE("NOT LATE ", 0))

I also updated your spreasheet Col K GoodJuJuand cell H8
enter image description here

Improve this answer

1 Comment

Thank you, GoodJuJu for taking time to update it on the sheet. Your solution is helpful, and I really appreciate the support! Have a great weekend!
0

If you are looking for a solution using Excel 365,

suppose you have given a name to the ideal date called Ideal_Days

you can try the following in cell H4 and drag it down to apply across:

=LET(x,Ideal_Days-G4,IF(x<0,"LATE ("&x&")","NOT LATE ("&x&")"))

Solution

Improve this answer

1 Comment

Thank you, I am using google sheets. While Erik's solution worked for me, I will try yours out as well. Really appreciate your help!

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.