SOLVED

Error subtracting times

Copper Contributor

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?

 

4 Replies
best response confirmed by soulzchaos (Copper Contributor)
Solution

@soulzchaos 

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

@soulzchaos 

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

@Sean689 

 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

1 best response

Accepted Solutions
best response confirmed by soulzchaos (Copper Contributor)
Solution

@soulzchaos 

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)

View solution in original post