[Micio]

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

[JBeaucaire]

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))

[Micio]

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

[KISS]

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'.

[Micio]

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

[JBeaucaire]

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.

[Micio]

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

[kalpit_84]

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.

[JBeaucaire]

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.