0

so I've created a google sheet wherein on Column A I have a date and a time (01/05/2023 14:46:89 | Yesterday's date and time) Something like this and on Column B, I have (01/06/2023 16:46:00 | Today's date and time)

What I want is to display the remaining time on Column C from column B time how many minutes before reaching the time in column A.

Lastly is, for example, I only have like 5 minutes left before reaching the same time on column A, I want to display a text on column D something like "almost there".

Is this even possible? Thank you.

I want to do it using an "ArrayFormula".

1

1 Answer 1

0

Put this formula in cell C2 to get the absolute difference between the datetime values in columns A and B:

=arrayformula( 
  if( 
    isnumber(A2:A) * isnumber(B2:B), 
    abs(timevalue(A2:A) - timevalue(B2:B)), 
    iferror(1/0) 
  ) 
)

...and this in cell D2:

=arrayformula( 
  if( 
    isnumber(C2:C) * (C2:C <= timevalue("0:05:00")), 
    "almost there", 
    iferror(1/0) 
  ) 
)

For this to work, columns C and D must be blank before you paste in the formulas.

See Working with date and time values in Google Sheets.

2
  • I have an error "TIMEVALUE parameter '08/01/2023, 09:12:00 am' cannot be parsed to date/time"
    – Albedo
    Jan 11, 2023 at 10:20
  • You can parse that value with timevalue(regexreplace(A2, "[^\d:/ ]", "")). Please ask only one question per post. Post a new question. Jan 11, 2023 at 10:56

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.