Amount of time between exact times

[shell2ri]

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!

 

[strooman]

	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))

 

[shell2ri]

	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.

 

[joeu2004]

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.

 

[strooman]

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.

 

[shell2ri]

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.

 

[shell2ri]

Thanks you SO much! That helped A LOT!

 

[shell2ri]

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

 

[shell2ri]

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

 

[joeu2004]

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.