Microsoft Access DateDiff函数跨天结束时间计算问题

Got it, let's break down what's happening here. Your current formula uses the same date column for both start and end times, so when the end time is something like 12:45 AM (next day), the combined end datetime ends up being earlier than the start datetime (e.g., 2024-05-20 00:45 vs. 2024-05-20 23:00), which is why you get a negative value. Since you can't modify the raw data, we need to adjust the calculation to account for this cross-day scenario.

Here are two reliable ways to fix this:

1. Use a Conditional Check (IIF/CASE) #

This method explicitly checks if the calculated duration is negative, and adds 24 hours to correct it if needed. The exact syntax might vary slightly depending on your database, but the core logic stays the same:

IIF(
  DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) < 0,
  (DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) / 3600) + 24,
  DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) / 3600
)

For databases that use CASE instead of IIF, it would look like this:

CASE
  WHEN DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) < 0
  THEN (DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) / 3600) + 24
  ELSE DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) / 3600
END

2. Use Modulo for a Concise Fix #

If you prefer a shorter formula, using modulo 24 will automatically wrap negative values into the correct positive duration:

(DateDiff('s', [日期列] + [开始时间], [日期列] + [结束时间]) / 3600 + 24) % 24

This works because adding 24 ensures even negative values become positive before applying modulo, which gives you the correct elapsed hours (e.g., -22.25 +24 =1.75, 1.75%24=1.75).

Example Walkthrough #

Let's say your date is 2024-05-20, start time is 23:00, end time is 00:45:

• Original calculation: (00:45 -23:00) in seconds /3600 = -22.25
• With conditional fix: -22.25 +24 =1.75 (correct 1 hour 45 minutes)
• With modulo fix: (-22.25 +24) %24 =1.75 (same correct result)

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