Hi,
I am trying to create a schedule for my employees and at the same time being able to calculate (or forcast for myself) the total hours that will be worked.

e.g.

A B C D E F G H I... P

1 NAME MONDAY TUESDAY WEDNESDAY THURSDAY... TOTAL
2 IN OUT IN OUT IN OUT IN OUT
3 JOHNY 6AM 2PM 6AM 2PM 1PM 9PM OFF... =((C3-B3+(C3<B3))*24)+((E3-D3+(E3<D3))*24)+...

It works perfectly, except, when I type in OFF the formula doesn't work... it shows #VALUE!

Is there a way I can type OFF instead of the time and the formula will still calculate the total hours per week?

Thank you

Add an IF(ISTEXT() check into each one of those segments to return a "0" value if text is found in either time cell.

=(IF(OR(ISTEXT(C3),ISTEXT(B3)),0,(C3-B3+(C3<B3))*24))

THANK YOU JB, IT WORKED GREAT!

I HAVE ONE MORE QUESTION:
How can I create the following formula:
IF B6 & B7 is text then 0, but if B6 is number then 1, and if both B6 & B7 are numbers then 2.

Is that possible? Thank you

isnumber(expression) and and(x1,x2,. xn) is a function so you can create what you want.

You could have also used in the previous question with if('OFF", 0, C2)

would have given the value of 0 if off or C2 if not equal to 'off'.

It doesn't want to work for me.

All I need is to come up with a formula that gives me the total number of employees per day.

In B1 - C1, B2- C2, B3- C3, B4- C4... etc. I am entering either (text) they are off or on vacation or time to come in and out.

How can I create a formula that will represents: if text or blank = 0
=IF(OR(ISTEXT(B7),ISTEXT(B8)),0,. and then if there is number or time entered please add 1... in adition the ones "1" will need to be also added together for each employee.

Thank you

I think you've overthought the problem. You don't need to test B6 and B7 for 0, you just need a TRUE/FALSE test on each cell for numerics. TRUE is the Excel equivalent of the number 1, so if you add TRUE+TRUE you get 2.

I think this is all you need:

=ISNUMBER(B6) + ISNUMBER(B7)

But, if for some reason you think you need to do the "text" test, too, which I don't think you do, then use this instead:

=IF(AND(ISTEXT(B6), ISTEXT(B7)), 0, ISNUMBER(B6) + ISNUMBER(B7))

But in my tests, these formulas behave exactly the same, making the second one unnecessary.

THANK YOU JBeaucaire. IT WORKS GREAT!!!! YOU ARE THE BEST. :) THANK YOU

Hello, JB

I typed the formula you have given it to me and it work for single time box. (Single day)

For multiple days I typed the following and gave me ans as "12:00 AM"

=(IF(OR(ISTEXT(C5),ISTEXT(B5)),0,(C5-B5+(C5<B5))*24))+(IF(OR(ISTEXT(E5),ISTEXT(D5)),0,(E5-D5+(E5<D5))*24))

Any idea what is wrong with this equation.

After first entering a formula in a cell that references other cells that have TIME values in them, Excel often changes the format of the formula cell to TIME as well.

Select the cell, press CTRL-1 to open the formatting window, change the NUMBER format back to General, or whatever else you may want it to be.