Error subtracting times

New 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?


2 Replies
best response confirmed by soulzchaos (New Contributor)


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