Forum Discussion
Solved
Error subtracting times
I am attempting to subtract times from one another to figure out how long something took. So Why do i get a value error when i try to subtract simple times from each other? (error says: A value used in the formula is of the wrong data type.) stranger still it only sometimes seems to do it because i was able to setup the first two lines (after playing with them for a bit they had same issue no idea how they fixed themselfs) (see picture)
Now all the error cell is doing is this: =B4-C4 (did not matter if i use auto sum or add ( ) )
yet it displays a error value. It does not seem to matter how i have these cells formatted (what time format is used)
What could i be doing wrong?
The values in the Time Arrived and Time Out columns are left-aligned. This is usually a sign that Excel sees the values as text, not as real times. Insert a space between the time and the am/pm indication:
11:00 am
4:00 pm
Also, use =C4-B4 instead of =B4-C4.
If there is a chance that Time Out will be after midnight, use =MOD(C4-B4,1)
4 Replies
The values in the Time Arrived and Time Out columns are left-aligned. This is usually a sign that Excel sees the values as text, not as real times. Insert a space between the time and the am/pm indication:
11:00 am
4:00 pm
Also, use =C4-B4 instead of =B4-C4.
If there is a chance that Time Out will be after midnight, use =MOD(C4-B4,1)
- Ohhhh adding a space. that worked perfectly thanks for your assistance
Good day
It seems I am experiencing a similar problem.
I did what was said to the previous user, but I keep getting hashtags in the designated cells.
Even the same reference cell is/was being used - upon selecting the cell, the box turns red.
Even more strange, it was working perfectly on the right side of the spreadsheet, until I shifted to the left.
I made sure number format was the same for everything.
Thanks in advance
