SOLVED

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

Copper 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
best response confirmed by allyreckerman (Microsoft)
Solution

@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!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@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.

View solution in original post