# Elapsed Time

#### tly0227

##### New Member
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

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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

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

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?

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.

Replies
8
Views
169
Replies
0
Views
622
Replies
5
Views
246
Replies
7
Views
151
Replies
2
Views
141

1,196,440
Messages
6,015,284
Members
441,886
Latest member
fbell

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back