Categorizing Hours (in military time) from Range -- Please Help!

[lawrencecis]

Hi everyone,

I'm having an interesting but difficult issue. I've searched Google and this forum, but still can't figure it out.

I am taking ranges of time and then need to break them down into two categories -- day time hours and night time hours -- and need to repeat this process daily. I am inputting a range, like 6:16:10 - 23:57:04. The problem I am facing is that even though I do an "if" function,<j37),"1", 2))="" the="" function="" is="" activated="" for="" number="" of="" beginning="" range="" (i.e.="" 6:16:10),="" but="" not="" all="" numbers="" inside="" range.="" example,="" i="" would="" like="" excel="" to="" recognize="" time="" from="" 23:00:00-23:57:04="" or="" difference="" (00:57:04)="" should="" be="" designated="" at="" "night="" hours"="" in="" order="" multiplied="" by="" $1.=""
the "if" function is only taking into account the first number in the range (i.e. 6:16:10). I would like the new function to be able to categorize/recognize all the time during a specific range as "night" or "day" time, like 23:00:00 - 23:57:04 would be marked as "night" and then I would be able to multiple it by $1. </j37),"1",>
<j37),"1", 2))="" the="" function="" is="" activated="" for="" number="" of="" beginning="" range="" (i.e.="" 6:16:10),="" but="" not="" all="" numbers="" inside="" range.="" example,="" i="" would="" like="" excel="" to="" recognize="" time="" from="" 23:00:00-23:57:04="" or="" difference="" (00:57:04)="" should="" be="" designated="" at="" "night="" hours"="" in="" order="" multiplied="" by="" $1.=""

If anyone could help I would appreciate it. </j37),"1",>

 

[bschwartz]

Hi,

Try this:

Excel 2010

	N	O
Time	Category
Day Hours
Day Hours
Day Hours
Night Hours
Night Hours

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6:00[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]5:00[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]8:00[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]24:00:00[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]23:00[/TD]

</tbody>

Time​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O18[/TH]
[TD="align: left"]=IF(AND(N18>=0.208333333333333,N18<0.958333333333333),"Day Hours","Night Hours")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lawrencecis]

Thanks bschwartz for the response. I really appreciate it.

I was able to do your function but it's not exactly what I was looking for. I would like to have a range of time be designated as "night" or "day." For example, if I have a starting time of 21:50:00 and an end time of 23:10:00, I would like the function to differentiate the entire amount of time and minutes in between those times (not just the beginning and ending numbers) to be designated as "night" or "day". For example, with example I just provided, I would like all the time between 21:55:00 and 22:59:59 marked as "day" and all the time between 23:00:00 and 23:10:00 marked as "night." With the function you provided only the first number in the range would be "night" or "day."

 

[barry houdini]

What are the night hours? Assuming 23:00 to 07:00 try this:

A2 = start time
B2 = end time

Use this formula in C2 to get the day hours as a decimal

<a2)*(23-7)+median(b2*24,7,23)-median(a2*24,7,23)
=(B2< A2)*(23-7)+MEDIAN(B2*24,7,23)-MEDIAN(A2*24,7,23)

and then in D2 for the rest of the hours (night hours)

=MOD(B2-A2,1)*24-C2

format C2 and D2 as number</a2)*(23-7)+median(b2*24,7,23)-median(a2*24,7,23)

 

[bschwartz]

Barry, he had originally posted that it was 23:00 to 5:00, but he edited the post numerous times, so it's hard to follow the thread. Slightly annoying...

 

[lawrencecis]

Thank you Barry Houdini! It worked. WOW.

One last thing, if I put whole numbers like 10:00:00 and 12:00:00 for the A2 and B2 cells it works smoothly. However, when I put in a fraction of time like 3:12:34 and 12:22:13 for the A2 and B2 cells, I get the #VALUE! sign. Is there a way I can format it to avoid this?

 

[lawrencecis]

Again, I apologize for posting twice.

@Houdini: I changed the format of the function you provided so that it matches the "night" hours I need -- no problem there.

 

[barry houdini]

One last thing, if I put whole numbers like 10:00:00 and 12:00:00 for the A2 and B2 cells it works smoothly. However, when I put in a fraction of time like 3:12:34 and 12:22:13 for the A2 and B2 cells, I get the #VALUE! sign. Is there a way I can format it to avoid this?

Did you resolve this? I can't replicate the problem - you should be able to put any time values in A2 and B2

 

[lawrencecis]

Did you resolve this? I can't replicate the problem - you should be able to put any time values in A2 and B2

Hi barry,

Unfortunately the problem still remains. I'm not sure the reason. I've been playing around with the formatting, but to no avail.

Thank you very much for your help.