0

Google Sheets

Hello. I have a Google Sheets here and I need to calculate the entire of column of "1" "2" to "3" which is next to 1 & 2. Currently, the third column displays the time but I would like it to display text telling me if it's past the time of box 2.

To be more specific, column 1 tells me the time of when something happened and column 2 takes the time next to it and adds 4 hours, or it should anyways. I would like column 3 to tell me in real time (AEDT timezone) when the time has passed from column 2.

Not required, but the second image provided shows a table with something that I'd like. The time I've recorded something of was 2:34 PM in the first column, so the second column takes that time and adds 4 hours (6:34 PM or 18:34) and if the time is actually passed 6:34 PM or 18:34, the third column would show some words or basically anything obvious that tells me if the time has passed from column 2.

Really difficult to explain... Let me know if you have questions.

enter image description here

1
0

You can get a dateserial value that represents the current time of the day in the timezone of the spreadsheet like this:

=timevalue(now())

Format the cell as Format > Number > Time to make it readable. To change the timezone of the spreadsheet, choose File > Spreadsheet settings.

You can compare the values in column B2:B to the current time of the day with this formula in cell C2:

=arrayformula( 
  if( 
    isnumber(B2:B) 
    * 
    (B2:B <= timevalue(now())), 
    "passed", 
    iferror(1/0) 
  ) 
)

It is an array formula that fills all of the column automatically, so you should clear the column before applying the formula to make room for the results.

To control how often the results are updated, choose File > Spreadsheet settings > Calculation.

See this answer for an explanation of how date and time values work in spreadsheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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