Count of hours within time range

[Chartley86]

Column D contains a list of time ranges e.g. D1 is 16:00-22:00, i need a formula to return the amount of full, consecutive hours within another time range, this will always be 20:00-06:00. So for the example D1 i would need a return of 2 hours.
Thanks

 

[Joe4]

Welcome to the Board!

By 20:00-06:00, do you always means 8:00 PM until 6:00 AM the next day (so, it is only that overnight, and does not span multiple days)?
Are your entries in D1 always entered like this: 16:00-22:00, meaning that they are always Text entries (and not actual date/time entries)?
Is there ever any date component to be considered in this calculation?

 

[Joe4]

Also, if it does span the overnight hours (hence spanning two days), if we only have a time range, how are we to know if it is for Day 1 or Day 2, i.e.
an entry like 04:00-6:00 or 04:00-20:00?

Perhaps it would be helpful if you could post a bunch of examples, and your expected output in each one.

 

[Chartley86]

The contents of D1, I have concatenated from B1 16:00 and C1 22:00 (I think they are in time format), happy to use a formula based on these if that is easier than using D1. It is very unlikely to span two days and yes I only need to find the hours between 8pm and 6am. Some further examples:

00:00 - 08:00 I would expect a return of 6
09:00 - 04:00 I would expect a return of 8
21:50 - 01:00 I would expect a return of 3 (I only need full hours)
04:00 - 06:00 I would expect 2
04:00 - 20:00 would also be 2

 

[Joe4]

Try this formula:

=MIN(FLOOR(C2+1,"00:60"),30/24)-MAX(CEILING(IF(C2>B2,B2+1,B2),"00:60"),20/24)

 

[Chartley86]

Thank you that's great. The only anomaly i have is when the person has clocked on but forgotten to clock off, so c2 is empty. For these ones the forumla seems to be calculating 4 hours but it should be none or error or n/a etc.

 

[Chartley86]

Also it isn't giving me a value when C2 is 22:00 (which should be 2 hours) or 23:00 (3 hours)

Many thanks

 

[Joe4]

Try this:

=IF(LEN(C2)=0,"None",IF(C2>(20/24),(FLOOR(C2,"00:60")-(20/24))+((6/24)-MIN(CEILING(B2,"00:60"),(6/24))),MIN(FLOOR(C2+1,"00:60"),30/24)-MAX(CEILING(IF(C2>B2,B2+1,B2),"00:60"),20/24)))

 

[Chartley86]

The 2nd formula gives "none" when c2 is blank but doesn't seem to work otherwise e.g 16:00-20:00 is now returning -0.83333 instead of 4

Actually i can filter out any blanks in c2 before applying the formula, so the first formula was good apart from it didnt work for times ending 22:00 or 23:00?

Thanks

 

[Joe4]

The 2nd formula gives "none" when c2 is blank but doesn't seem to work otherwise e.g 16:00-20:00 is now returning -0.83333 instead of 4

According to your rule, 16:00 - 20:00 should actually be returning 0. We are looking for the hours between 20:00 - 6:00. There are 0 hours between 16:00 - 20:00 that fall in that range.

We only need to make one small edit to the last formula to accommodate that:

=IF(LEN(C2)=0,"None",IF(C2>[COLOR=#ff0000]=[/COLOR](20/24),(FLOOR(C2,"00:60")-(20/24))+((6/24)-MIN(CEILING(B2,"00:60"),(6/24))),MIN(FLOOR(C2+1,"00:60"),30/24)-MAX(CEILING(IF(C2>B2,B2+1,B2),"00:60"),20/24)))