Hey there, let's sort out that frustrating ####### error you're seeing when calculating remaining time! First, let's break down why this might be happening, then walk through reliable alternative methods to get the correct remaining duration.

Why You're Seeing ####### #

The most likely culprits here are two key issues:

1. Wrong function for current time: TODAY() only returns the current date (no time component). If you're using it to get the current time, you're missing the hour/minute/second data Excel needs for accurate time difference calculations.
2. Incorrect cell formatting: Even if your formula is right, if the cell isn't set to display time durations properly (or the cell width is too narrow), Excel will show ####### instead of the actual value.

Alternative Calculation Methods #

Let's fix this step-by-step:

1. Use the Right Function for Current Time #

Replace TODAY() with NOW() in your "actual time" cell. NOW() returns the full current date and time (e.g., 2024/10/23 9:42:00 AM), which is exactly what you need for time difference math.

2. Calculate Remaining Time with These Formulas #

Assume:

• Cell A1 = Current time (using =NOW())
• Cell B1 = Your end time (e.g., manually entered 05:00:00 PM)

Option 1: Simple Time Difference

Use this straightforward formula to get the remaining time:

=B1 - A1

Then, format the result cell with a custom time format:

• Right-click the cell → Format Cells → Custom → Enter [h]:mm (the brackets ensure Excel displays hours correctly even if the duration exceeds 24 hours, which works perfectly for your 7:18 case).

Option 2: Ignore Date Components (For Same-Day Calculations)

If you want to ensure you're only calculating time within the same day (even if NOW() includes a date), use this formula to isolate just the time parts:

=B1 - MOD(A1, 1)

MOD(A1, 1) extracts the time portion from NOW() (since dates in Excel are stored as whole numbers, and times as decimals). This guarantees you're only comparing times, not dates.

Option 3: Hardcode the End Time (If It's Fixed)

If your end time is always 5:00 PM, you can skip manual entry and use the TIME() function directly:

=TIME(17, 0, 0) - MOD(NOW(), 1)

TIME(17,0,0) creates the 5:00 PM time value, so you don't have to rely on manual input.

Quick Troubleshooting Tip #

If you still see ####### after trying these formulas:

• Double-check that the result cell is wide enough to display the time (drag the column edge to widen it).
• Ensure your end time is later than the current time (a negative time difference will also trigger ####### unless you enable negative time formatting, but that shouldn't be an issue here).

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