if-statementgoogle-sheetsformattinggoogle-sheets-formulatimeval

IF function to calculate duration (Hours:Minutes:Seconds)

I use Importxml to extract the clock from a website that tells the date and time that the market will close ... Well, using the Now () function, I calculate the imported clock minus the current time. I converted the cell with the result of the calculation to the duration format (This way I see how much time is left for the market to close).

=importxml("https://cartolafcmix.com/","//div[@class='content']/span/@data-timer")

=NOW()

=A1-A2 (Duration Formatted Cell)

I am trying to use the IF function so that if it is more than 22:00:00 and less than 23:00:00, the cell gets written (Alert 22 hours)

=IF(AND($A$3>"22:00:00",$A$3<"23:00:00"),"Alert 22 Hours","Error")

At the moment are missing 22:18:25 for the market to close, but the result of the formula is giving "error", I do not know what I may be wrong.

Solution

  • try:

    =IF(TEXT(A3, "hh")="22", "Alert 22 Hours", A3)

    0

    =IF((TIMEVALUE(A3)>TIMEVALUE("22:00:00"))*
    (TIMEVALUE(A3)<TIMEVALUE("23:00:00")), "Alert 22 Hours", A3)

    0

    one-cell solution:

    =IF(TEXT(IMPORTXML("https://cartolafcmix.com/", 
 "//div[@class='content']/span/@data-timer")-NOW(), "[hh]")="22", "Alert 22 hours", 
    TEXT(IMPORTXML("https://cartolafcmix.com/", 
 "//div[@class='content']/span/@data-timer")-NOW(), "[hh]:mm:ss"))

    0