# Amount of time between exact times

#### shell2ri

##### New Member
Hi, Can someone please help me build a formula that can get the time between times, i.e. 8:30am to 2:30pm AND only between the times of 8:30am and 2:30pm. For example, some people might come in at 8:20am or leave at 2:40pm. I only want it to count the time they come in after 8:30am and before 2:30pm - not the extra minutes. Thanks so much!

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
 A B C D F 1 Lower Upper start stop total 2 8:30 14:30 8:45 17:15 5,75

<tbody>
</tbody>

Formula in F2:
=SUM(MAX((B2-A2-MAX(0,C2-A2)-MAX(0,B2-D2))*24,0))

 A B C D F 1 Lower Upper start stop total 2 8:30 14:30 8:45 17:15 5,75

<tbody>
</tbody>

Formula in F2:
=SUM(MAX((B2-A2-MAX(0,C2-A2)-MAX(0,B2-D2))*24,0))

I'm a little confused on what the total is...what is 5,75? With the start and stop times you gave I would think it would be 6 hours and 45 minutes because they are starting 15 minutes late and leaving after 2:30pm so their 7 hour workday would be 15 minutes less than the 7 hour workday. Sorry if my post confused you.

Can someone please help me build a formula that can get the time between times, i.e. 8:30am to 2:30pm AND only between the times of 8:30am and 2:30pm. For example, some people might come in at 8:20am or leave at 2:40pm. I only want it to count the time they come in after 8:30am and before 2:30pm - not the extra minutes.

Is the start and end time always within the same day? That is, midnight is not in between?

If that is the case, then if a person's start and end times are in A1 and A2:

=MIN(A2,TIME(14,30,0))-MAX(A1,TIME(8,30,0))

That 14:30 is 2:30 PM.

ADVERTISEMENT
I'm a little confused on what the total is...what is 5,75?
That is 5 hour and 45 minutes.

With the start and stop times you gave I would think it would be 6 hours and 45 minutes because they are starting 15 minutes late and leaving after 2:30pm so their 7 hour workday would be 15 minutes less than the 7 hour workday. Sorry if my post confused you.

Well, in your first post you explained that you want to count the number of hours that falls between "8:30am to 2:30pm AND only between the times of 8:30am and 2:30pm." so I presumed that your Lower Limit is 8:30AM and your Upper Limit is 02:30PM. That said, if you start at 08:00AM and stop at 10:00AM, one and a half hour (1,5) fall wthin your given time boundery: (8:30AM - 10:00AM)

In my previous example every hour after 02:30PM doesn't count, except (as joeu2004 point out) as the time expands midnight and overlaps the next day.

Yes, I'm sorry military time is 14:30 = 2:30 in the afternoon. My apologizes.

Is the start and end time always within the same day? That is, midnight is not in between?

If that is the case, then if a person's start and end times are in A1 and A2:

=MIN(A2,TIME(14,30,0))-MAX(A1,TIME(8,30,0))

That 14:30 is 2:30 PM.

ADVERTISEMENT
Thanks you SO much! That helped A LOT!

Is there any way I can change that 5,75 to hours and minutes? (for clarity purposes) Thanks!

If not, that's okay! What you provided was excellent!

For example, some people might come in at 8:20am or leave at 2:40pm. I only want it to count the time they come in after 8:30am and before 2:30pm - not the extra minutes.
If that is the case, then if a person's start and end times are in A1 and A2:
=MIN(A2,TIME(14,30,0))-MAX(A1,TIME(8,30,0))
[Note t]hat 14:30 is 2:30 PM.
Is there any way I can change that 5,75 to hours and minutes? (for clarity purposes) Thanks!

You seem to be responding to me, but I never said anything about 5.75 hours. (Note: I use period as the decimal point.)

In fact, if you only want to count time between 8:30 and 2:30 PM, that is 6 hours, not 5.75 or 6.75 (6h 45m).

I should have noted that for my formula, you might need to format the cell as Custom h:mm to see the time in the form 6:00.

Replies
6
Views
284
Replies
3
Views
2K
Replies
5
Views
1K
Replies
12
Views
820
Replies
2
Views
525

Threads
1,196,252
Messages
6,014,265
Members
441,809
Latest member
pawansher2002

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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