Excel中如何编写公式判断LAST_UPDATE_DATE是否晚于CREATION_DATE+24小时

Got it, let's break down how to write the formula you need. The core logic here is checking if LAST_UPDATE_DATE is later than CREATION_DATE plus 24 hours, and returning "F" when it's not (which matches your two test cases).

Direct & Reliable Formula (Using Raw Date-Time Values) #

Instead of relying on the text-formatted Date_Diff column, it's better to use the original date-time cells directly—Excel stores dates/times as numerical values where 1 = 24 hours, so this avoids any text conversion issues.

Assuming:

• LAST_UPDATE_DATE is in cell I2
• CREATION_DATE is in cell AE2

Use this formula for the response_in_time column:

=IF(I2 > AE2 + 1, "T", "F")

How It Works #

• AE2 + 1 adds exactly 24 hours to the creation date (since 1 in Excel's date system equals one full day).
• The IF function checks if the last update date is later than that 24-hour threshold:
  • If yes (update was more than 24 hours after creation), it returns "T" (you can replace this with any value you need for that scenario).
  • If no (update was within 24 hours of creation, like your two test cases), it returns "F" as requested.

Verification with Your Test Cases #

Let's confirm this works for your data:

1. First row: I2 = 10/9/2021 4:42:46 PM, AE2 = 10/9/2021 3:25:36 PM
   • AE2 + 1 = 10/10/2021 3:25:36 PM
   • I2 is earlier than this threshold → returns "F"
2. Second row: I2 = 10/13/2021 1:52:12 PM, AE2 = 10/13/2021 10:53:07 AM
   • AE2 + 1 = 10/14/2021 10:53:07 AM
   • I2 is earlier than this threshold → returns "F"

Alternative (Using Your Date_Diff Column) #

If you prefer to use the existing Date_Diff column (formatted as [h]:mm:ss text), you can convert it back to a numerical value and check against 1 (24 hours):

=IF(VALUE(Date_Diff_Cell) > 1, "T", "F")

Just replace Date_Diff_Cell with the cell containing your calculated time difference (e.g., J2 if it's in column J). Note that this is less reliable than using raw date-times, as text formatting can sometimes cause conversion errors.

内容的提问来源于stack exchange，提问作者AlexWithYou