Elapsed Time

[tly0227]

I have a spreadsheet that I am trying to calculate the elapsed time between a start and end time. This is a spreadsheet that I get from IT that shows when a student has logged in and out at our tutoring center on campus. After I have the elapsed time in h:mm format, I want to convert that to decimal format. I figured that part out, I just can't get the elapsed time to calculate right. Here is an example of how my sheet is set up.

A-C Columns are irrelevant for this calculation.

D E F G
1 Sign In Sign Out Elapsed Time Time Decimal
2 8:36 AM 10:24 AM =F2*24
3 10:50 AM 12:03 PM =F3*24


This goes on for over 2000 rows. So I really want this to work. If I put a time in column F, G is calculating correctly, so no issues there. I have column F formatted as Custom> h:mm. The sign in and sign out times for each row are never going to be on different days, so Sign In time will always be earlier than Sign Out time. I read somewhere online to use the formula =MAX(D2:E2)-MIN(D2:E2), but that didn't work. When I do that, I just get the result 0:00. I've tried =E2-D2 as well but that gives me #VALUE. I'm sure it's probably something in the formatting of the D and E cells, but I've tried a whole bunch of different options and nothing changes what I'm getting in F.

Any help is greatly appreciated. I'm about ready to pull my hair out over this. I'm pretty decent at Excel, but every once in a while I gotta do something that just doesn't work like I want it to.

Thanks.

 

[tly0227]

I just noticed the spacing didn't keep when I posted this, so to clarify: Column D is the sign in time, Column E is the sign out time, Column F is the elapsed time, and Column G is the Time Decimal.

 

[barry houdini]

Yes, =E2-D2 would normally be fine assuming the times are on the same day - if that doesn't work then that probably means that E2 and D2 (or at least one of them) don't contain valid times - can you check whether there are any additional characters at the start or end of the "times", you may need to eliminate those somehow

 

[tly0227]

It appears that there is a space in front of every sign in and sign out time. Is there a quick, easy way to remove that space?

 

[tly0227]

Figured it out, just a did a find for " 1" and replaced it with "1"...and then did that for every number through 9. That fixed it all though. Thanks for your help.