SOLVED

Error subtracting times

%3CLINGO-SUB%20id%3D%22lingo-sub-2383040%22%20slang%3D%22en-US%22%3EError%20subtracting%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383040%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20attempting%20to%20subtract%20times%20from%20one%20another%20to%20figure%20out%20how%20long%20something%20took.%20So%20Why%20do%20i%20get%20a%20value%20error%20when%20i%20try%20to%20subtract%20simple%20times%20from%20each%20other%3F%20(error%20says%3A%20A%20value%20used%20in%20the%20formula%20is%20of%20the%20wrong%20data%20type.)%20stranger%20still%20it%20only%20sometimes%20seems%20to%20do%20it%20because%20i%20was%20able%20to%20setup%20the%20first%20two%20lines%20(after%20playing%20with%20them%20for%20a%20bit%20they%20had%20same%20issue%20no%20idea%20how%20they%20fixed%20themselfs)%20(see%20picture)%3C%2FP%3E%3CP%3ENow%20all%20the%20error%20cell%20is%20doing%20is%20this%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DB4-C4%20(did%20not%20matter%20if%20i%20use%20auto%20sum%20or%20add%20(%20)%26nbsp%3B%20)%3C%2FP%3E%3CP%3Eyet%20it%20displays%20a%20error%20value.%20It%20does%20not%20seem%20to%20matter%20how%20i%20have%20these%20cells%20formatted%20(what%20time%20format%20is%20used)%3C%2FP%3E%3CP%3EWhat%20could%20i%20be%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2383040%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2383074%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20subtracting%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1062485%22%20target%3D%22_blank%22%3E%40soulzchaos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20values%20in%20the%20Time%20Arrived%20and%20Time%20Out%20columns%20are%20left-aligned.%20This%20is%20usually%20a%20sign%20that%20Excel%20sees%20the%20values%20as%20text%2C%20not%20as%20real%20times.%20Insert%20a%20space%20between%20the%20time%20and%20the%20am%2Fpm%20indication%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E11%3A00%20am%3C%2FP%3E%0A%3CP%3E4%3A00%20pm%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%2C%20use%20%3DC4-B4%20instead%20of%20%3DB4-C4.%3C%2FP%3E%0A%3CP%3EIf%20there%20is%20a%20chance%20that%20Time%20Out%20will%20be%20after%20midnight%2C%20use%20%3DMOD(C4-B4%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2383140%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20subtracting%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383140%22%20slang%3D%22en-US%22%3EOhhhh%20adding%20a%20space.%20that%20worked%20perfectly%20thanks%20for%20your%20assistance%3C%2FLINGO-BODY%3E
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)
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