Counting Hours in a Schedule

[mike4777]

I was wondering if someone could help me develop a formula to count the hours when formatted in such a way:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Hours[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]=Formula[/TD]
[TD]8am-8pm[/TD]
[TD]10:30pm-6:15am[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I can do it with a million LEft( and Right( but that seems terribly clunky and prone to possible error.

 

[mike4777]

If that formula is meant for the cells when formatted as "12:00 am" then no it does not work. I am returning a number but it is not the number of hours. Also, your format "[H]:mm" I changed to h.m

I do not want 8:30 (to represent 8.5 hours). I want it to say 8.5.

 

[mike4777]

so I guess the m needs to be converted into fraction

 

[Zack Barresse]

No, doesn't need converted to a fraction. It's represented as a time value, which is a fraction of a whole number. In Excel, on day is represented as a whole number. So half a day (12 hours) is represented as 0.5. If expressed as "[h]:mm" format it would be "12:00", or 12 hours, which is the equivalent of half a day. If it was 6 hours, the decimal equivalent would be 0.25, or 6:00 hours, which is the same value.

If you want to convert a time value to a numerical decimal value, you'll need to multiply the entire value by 24, since there are 24 hours in a day. So taking a time value of 8 hours, which is expressed as "8:00", and as a decimal is 0.3333333, would be to wrap the entire formula in (YOUR_PROVIDED_FORMULA)*24. You would need to ensure you formatted that cell/column in a number format, not a time format.

 

[vogel997]

[TABLE="width: 344"]
<colgroup><col width="64" style="width: 48pt;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 475;"> <tbody>[TR]
[TD="width: 458, bgcolor: transparent, colspan: 8"]The mod will help to shorten the formula.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"]See file attached
http://1drv.ms/1qDWcB7


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Zack Barresse]

No, that's not true vogel997. The IF structure is so long because it checks to ensure two values are entered for each day. Remove them and the formulas are about the same length. But I would keep the IF statements in, because they give a check the other formulas do not.

 

[mike4777]

Everything seems to be working great now except one thing.

When I set a time value for say, 9:00 PM - 12:00 AM it does not count it as 3 hours because 12:00 AM is treated as a lesser value than 9:00 PM.

IF(COUNTA(D4,F4)=2,(F4<=D4)+F4-D4,0)

I need it to take in to account that if the second time slot passes 11:59 PM, it knows that it is the next day.

 

[mike4777]

Everything seems to be working great now except one thing.

When I set a time value for say, 9:00 PM - 12:00 AM it does not count it as 3 hours because 12:00 AM is treated as a lesser value than 9:00 PM.

IF(COUNTA(D4,F4)=2,(F4<=D4)+F4-D4,0)

I need it to take in to account that if the second time slot passes 11:59 PM, it knows that it is the next day.

never mind, I was mistaken. Thank you guys for helping with this. I was a complete newb when it comes to dealing with dates.

 

[Zack Barresse]

Glad to help.