Excel COUNTIFS函数计算当月剩余班次（自定义日期格式限制）

Got it, let's work through this problem to get your remaining shift count right. Here's what you need to know and adjust:

First, Understand the Core Issue with Your Current Formula #

Your existing formula uses ">=TODAY()" as a static text string, but Excel needs to evaluate the TODAY() function dynamically to compare dates correctly. That's likely why it's not returning the accurate number of shifts.

The Corrected Formula #

Use this adjusted version instead:

=COUNTIFS($A$6:$A$36, ">="&TODAY(), B$6:B$36, ">0")

Breakdown of Each Part #

• $A$6:$A$36, ">="&TODAY(): This filters rows where the date in column A is today or later. The & operator connects the comparison string ">=" to the TODAY() function, so Excel treats it as a live date comparison instead of trying to match the literal text ">=TODAY()".
• B$6:B$36, ">0": This narrows results to only rows where there's an actual scheduled shift (hours greater than 0) in column B. The mixed reference B$6:B$36 lets you copy this formula horizontally to columns C, D, etc., without breaking the fixed row range.

Critical Check: Ensure Column A Contains Date Values (Not Text) #

Even though your dates display as "Fri, 20/04/2018", Excel needs them stored as date values (not plain text) for the comparison to work. Here's how to verify and fix this if needed:

1. Select a cell in column A and check the formula bar. If it shows a standard date (like 2018/4/20), you're set—your custom format only changes the display, not the underlying value.
2. If the formula bar shows the same text as the cell display (e.g., "Fri, 20/04/2018"), convert it to a date value without altering the display format:
   • Use =DATEVALUE(A6) in a blank cell to turn the text into a date.
   • Copy the converted values, then paste them back into column A as values.
   • Reapply your custom date format (Fri, dd/mm/yyyy)—this won't affect the underlying date value, just how it's shown.

Final Notes #

Since your column A only includes dates from the current month, this formula will automatically count all valid shifts from today through the end of the month. No extra conditions for the month itself are needed!

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