如何在MS Access 2010中拼接文本框以使用DateDiff函数

Hey there! Let's work through this issue step by step—you're hitting that #Name error and having trouble formatting the final time, so let's fix both problems.

First: Why the #Name Error? #

The DateDiff function you're using is a VBA/Access function, not an Excel worksheet function. Excel doesn't recognize it in cell formulas, which is exactly why you're seeing that error. We'll swap that out for Excel-native calculations instead.

Fix 1: Calculate Total Working Minutes Correctly #

First, let's get the total working minutes right. Excel stores time as a decimal (1 full day = 1, so 1 minute = 1/1440). Here's how to calculate the total working minutes:

• If you're using cell references (e.g., start time in A1, end time in B1, rest minutes in C1):

  =(B1 - A1)*1440 - C1
  

• If you're hardcoding the values like in your example:

  =("16:00" - "08:00")*1440 - 30
  

  Quick note: Your original formula had 30*60—that would subtract 1800 minutes (30 hours!) instead of 30 minutes, so we just subtract 30 directly here.

Fix 2: Convert Minutes to Hours:Minutes Format #

Now let's turn that total minute count into a clean hh:mm format. You have two solid options:

Option 1 (Simpler: Use TEXT Function)

This is the easiest way—we convert the total minutes back to a decimal time value, then format it with TEXT:

=TEXT((("16:00"-"08:00")*1440 - 30)/1440, "[h]:mm")

The [h] lets Excel display hours longer than 24 (if your workdays ever go over that). If you know you'll never exceed 24 hours, you can use "h:mm" instead.

Option 2 (Your Original Idea, Fixed)

If you want to stick with the division/MOD approach, here's the corrected version (we add INT to get whole hours, and TEXT to ensure minutes show as two digits):

=INT((("16:00"-"08:00")*1440 - 30)/60) & ":" & TEXT(MOD((("16:00"-"08:00")*1440 - 30),60), "00")

This will output something like 7:30 for your example (since 8 hours minus 30 minutes is 7 hours 30 minutes).

Testing It Out #

For your specific values (start 08:00, end 16:00, rest 30 mins), both formulas will give you the correct working duration in a clean hour-minute format.

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