Figuring total # of hours between multiple times

[R P Wilson]

I am creating a work schedule template that will total the number of hours an
individual is scheduled to work for the week. The template will reflect the
work schedule for two weeks for a number of individuals. I have entered a
formula that will calculate the total if the cell contents are all time
values. My problem is that any given day can have "OFF", "VAC" or "HOL" in
one of the cells. This returns a "#VALUE!" error. Is there any way to imbed
an additional formula to ignore any cells with text or even with the specific
values listed above. Here is a copy of the information I am working with:
15-Jul 16-Jul 17-Jul Hours
06:00 16:00 07:00 16:00 OFF #VALUE!

The formula enter is: =((K3-J3)*24)+((M3-L3)*24)+((O3-N3)*24)

 

[Per Jessen]

Hi

If text is only entered in K3, M3 or O3, try this:

=IF(ISNUMBER(K3),K3-J3,0)+IF(ISNUMBER(M3),M3-L3,0)+IF(ISNUMBER(O3),O3-N3,0)

Regards,
Per

 

[T. Valko]

Provided that none of the time ranges span past midnight. For example:

5:00 PM - 1:00 AM

=(SUM(O3,M3,K3)-SUM(N3,L3,J3))*24

Format as General or Number

 

[R P Wilson]

The time ranges DO span past midnight.

Provided that none of the time ranges span past midnight. For example:

5:00 PM - 1:00 AM

=(SUM(O3,M3,K3)-SUM(N3,L3,J3))*24

Format as General or Number

 

[R P Wilson]

Thanks for the reply; however, the formula returns the value "0" if any of
the cells are blank or contain text.

 

[T. Valko]

The time ranges DO span past midnight.

In that case, I think you'd be better off doing a daily total and then
getting the weekly/bi-weekly total from the daily total. If you wnat to use
a single formula that accounts for both text entries like Vac and has to
account for times past midnight, that formula would be long and somewhat
complex. For example, array entered** :

=IF(MOD(COUNT(J3:Q3),2)=1,"",(SUM(IF(ISNUMBER(K3:Q3),IF(MOD(COLUMN(K3:Q3),2)=1,IF(K3:Q3<J33,K3:Q3+1,K3:Q3))))-SUM(IF(ISNUMBER(J33),IF(MOD(COLUMN(J33),2)=0,J33))))*24)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Not real pretty, is it? <g>

To get the daily total accounting for text entries and times past midnight:

J3 = 7:00 PM
K3 = 3:00 AM

=IF(COUNT(J3:K3)=2,MOD(K3-J3,1)*24,"")

Then the weekly/bi-weekly total would be a simple SUM formula:

=SUM(daily_total_range)