formulas in Excel

Question

Attempting to calculate hours spent in Excel. Cells formatted to h:mm. When I subtract beginning from time from end time I get a #Value error that says wrong format type, and the results cell is now formatted as general. I'm using a simple formula of =j5-g5. What am I doing wrong.

Answer 1

The #VALUE! error with =J5-G5 means at least one of the referenced cells is not a valid Excel time value, even if it looks like one or is formatted as h:mm.

Check and fix the following:

1. Confirm the cells are real times, not text
   • Select J5 and G5. On the Home tab, in the Number group, change the format to General.
   • If they are valid times, you will see decimal values (e.g., 0.5 for 12:00 PM).
   • If you still see something like 08:30 or the cell is left‑aligned, the value is stored as text and will cause #VALUE!.
2. Convert text times to real times
   For any cell that shows as text when formatted as General:
   • Re‑enter the time manually (e.g., type 8:30 and press Enter).
   • Or, if many cells are affected, use a helper column and a function that ignores text, such as =SUM(...), after cleaning the data as needed.
3. Check for hidden spaces or special characters
   • A time like "08:30 " (with a trailing space) or with hidden characters will also cause #VALUE!.
   • Use tools like Find and Replace to remove spaces, or functions such as CLEAN or REPLACE as described in the context to strip problematic characters, then convert the result to values.
4. Ensure the result cell is a time format
   After J5 and G5 are valid times, =J5-G5 will return a numeric result. Format the formula cell as h:mm (or a custom time format) so the duration displays correctly.

If the error persists even with simple numeric subtraction (for example, =B1-A1 in a new workbook), check the Windows Region settings to ensure the minus sign is not configured as the list separator, as this can also cause #VALUE! in basic subtraction.

---

References:

• How to correct a #VALUE! error
• How to avoid broken formulas in Excel

Answer 2

I figured out I needed to add a space between the time and the am/pm. formula now works

Answer 3

answer was confusing