Help, Excel Noob, Shift Time Conversion to Negative???

%3CLINGO-SUB%20id%3D%22lingo-sub-2935092%22%20slang%3D%22en-US%22%3EHelp%2C%20Excel%20Noob%2C%20Shift%20Time%20Conversion%20to%20Negative%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935092%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20sorry%20to%20both%20you%20all%2C%20but%20I'm%20a%20novice%20when%20it%20comes%20to%20working%20with%20Excel.%20I'm%20trying%20to%20merge%20some%20excel%20sheets%20and%20have%20decided%20to%20do%20so%20from%20scratch%20to%20get%20a%20feel%20and%20understanding%20for%20the%20formula%20making%20and%20such.%3CBR%20%2F%3E%3CBR%20%2F%3EAnyway%2C%20so%20I'm%20filling%20out%20shift%20times%20as%20such%20below%3A%3CBR%20%2F%3E%3CBR%20%2F%3EShift%20Start%20.%20.%20.%20.%20.%20.%20.%20.%20Shift%20End%20.%20.%20.%20.%20.%20.%20.%20Total%20Hours%3CBR%20%2F%3E4%3A00%20PM%20.%20.%20.%20.%20.%20.%20.%20.%20.%204%3A30%20PM%20.%20.%20.%20.%20.%20.%20.%20.%20-15.31%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20I'm%20not%20understanding%20what%20I'm%20doing%20wrong%20that%20I'm%20getting%20a%20negative%20value%20rather%20than%20%220.5%22%20for%20a%20half%20hour%20completed.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20formula%20for%20Hours%20is%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3D%5B%40%5BShift%20End%5D%5D-%5B%40%5BShift%20Start%5D%5D*24%3CBR%20%2F%3E%3CBR%20%2F%3EWithout%20the%20*24%20the%20value%20is%20%220.02%22%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20been%20watching%20videos%20and%20trying%20to%20figure%20this%20out%20all%20night%2C%20can%20someone%20please%20help%20me%20out%3F%20What%20obvious%20thing%20am%20I%20missing%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2935092%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2935215%22%20slang%3D%22en-US%22%3ERe%3A%20Help%2C%20Excel%20Noob%2C%20Shift%20Time%20Conversion%20to%20Negative%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209843%22%20target%3D%22_blank%22%3E%40SWebster95%3C%2FA%3E%26nbsp%3BTry%20it%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D(%5B%40%5BShift%20End%5D%5D-%5B%40%5BShift%20Start%5D%5D)*24%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BI.e.%20put%20round%20brackets%20around%20the%20subtraction.%20Otherwise%2C%20you%20deduct%20the%20End%20time%20multiplied%20by%2024%20from%20the%20Start%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2935560%22%20slang%3D%22en-US%22%3ERe%3A%20Help%2C%20Excel%20Noob%2C%20Shift%20Time%20Conversion%20to%20Negative%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935560%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209843%22%20target%3D%22_blank%22%3E%40SWebster95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20further%20observation.%26nbsp%3B%20The%20%3CSTRONG%3E0.2%3C%2FSTRONG%3E%20(strictly%20speaking%20%3CSTRONG%3E0.020833%3C%2FSTRONG%3E)%20was%20perfectly%20correct%20as%20a%20proportion%20of%20the%20day.%26nbsp%3B%20The%20formatting%20could%20be%20more%20helpful%20though.%26nbsp%3B%20A%20custom%20number%20format%20%3CSTRONG%3E%5Bh%5D%3Amm%3C%2FSTRONG%3E%20will%20provide%20a%20meaningful%20display%20of%20%3CSTRONG%3E0%3A30%3C%2FSTRONG%3E.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hello,

I'm sorry to both you all, but I'm a novice when it comes to working with Excel. I'm trying to merge some excel sheets and have decided to do so from scratch to get a feel and understanding for the formula making and such.

Anyway, so I'm filling out shift times as such below:

Shift Start . . . . . . . . Shift End . . . . . . . Total Hours
4:00 PM . . . . . . . . . 4:30 PM . . . . . . . . -15.31

And I'm not understanding what I'm doing wrong that I'm getting a negative value rather than "0.5" for a half hour completed.

My formula for Hours is:

=[@[Shift End]]-[@[Shift Start]]*24

Without the *24 the value is "0.02"

I've been watching videos and trying to figure this out all night, can someone please help me out? What obvious thing am I missing?
4 Replies

@SWebster95 Try it like this:

 

=([@[Shift End]]-[@[Shift Start]])*24

 I.e. put round brackets around the subtraction. Otherwise, you deduct the End time multiplied by 24 from the Start time.

@SWebster95 

A further observation.  The 0.2 (strictly speaking 0.020833) was perfectly correct as a proportion of the day.  The formatting could be more helpful though.  A custom number format [h]:mm will provide a meaningful display of 0:30.  

This was exactly it. Thank you! I knew it was something simple too, haha. Just staring at it for too long I guess, didn't see it.
Yes, and I did have that as a back up option, but the decimal solution was what I was hoping to achieve. I appreciate the feedback though, thank you!