Show hours worked or Text "Day Off"

[srctr]

I have a spreadsheet that keeps track of hours worked during the week. The
formula is =IF(OR(B23="",C23="",D23="",E23=""),"Day Off",(TEXT(B23,"h:mm
AM/PM")&" - "&TEXT(C23,"h:mm AM/PM")
&"
"&TEXT(D23,"h:mm AM/PM")&" - "&TEXT(E23,"h:mm AM/PM")))


The columns track the In and Out times, Column B is In, Column C is Out,
Column D is In, Column E is Out.

This works great when there are no hours worked or there is a time in all 4
columns. It will give me the result as the hours in and out or Day Off. But
if there is time in for only half a day, it gives me Day Off.

For example: Monday has In as 8:30 am, Out as 12:00 pm, In as 12:30 pm, Out
as 5:00 pm the results show as:
8:30 am - 12:00 pm
12:30 pm - 5:00 pm

But if Tuesday has In as 8:30 am, Out as 12:00 pm, In is blank, Out is blank
then the results shows as Day Off

Obviously if Wednesday has In is blank, Out is blank, In is blank, Out is
blank the the results shows as Day Off

How do I get it to show me the hours worked even if some of the cells are
blank?

 

[barry houdini]

Try

=IF(LEN(B23&C23&D23&E23),IF(COUNT(B23,C23)=2,TEXT(B23,"h:mm AM/PM")&"
- "&TEXT(C23,"h:mm AM/PM "),"")&IF(COUNT(D23,E23)=2,TEXT(D23,"h:mm AM/
PM")&" - "&TEXT(E23,"h:mm AM/PM"),""),"Day Off")

 

[srctr]

That worked, After I tweaked it a little because the cut and paste added a
space in the formula.

Now in my other formula I also inserted an ALT-ENTER to make two lines. I
can't seem to figure where I place this break in your formula.

Where would I press ALT-Enter in the formula, or is there another way to
make it show each In and Out on its own line in the cell?

Like this
8:30 am - 12:00 pm
12:30 pm - 5:00 pm

Thanks

 

[barry houdini]

That worked, After I tweaked it a little because the cut and paste added a
space in the formula.  

Now in my other formula I also inserted an ALT-ENTER to make two lines.  I
can't seem to figure where I place this break in your formula.  

Where would I press ALT-Enter in the formula, or is there another way to
make it show each In and Out on its own line in the cell?

Like this
8:30 am - 12:00 pm
12:30 pm - 5:00 pm

Thanks
--
cao






- Show quoted text -

I wasn't sure if that was how you wanted it or just how it was
formatted here....

Try including a line break CHAR(10) in the formula

=IF(LEN(B23&C23&D23&E23),IF(COUNT(B23,C23)=2,TEXT(B23,"h:mm AM/PM")&"
- "&TEXT(C23,"h:mm AM/PM"),"")&IF(COUNT(D23,E23)=2,CHAR(10)&TEXT
(D23,"h:mm AM/PM")&" - "&TEXT(E23,"h:mm AM/PM"),""),"Day Off")

You should expand the cell width as wide as you need first and the use
Cell> Format > Alignment > Wrap Cells

 

[NickViv]

If you want me to write the entire system for you, get people voting
on:
http://excelexperts.com/node/16

Rgds

Nick

 

[T. Valko]

Try this (all on one line):

=IF(LEN(B23&C23&D23&E23)=0,"Day Off",
IF(COUNT(B23,C23)=2,TEXT(B23,"h:mm AM/PM")
&" - "&TEXT(C23,"h:mm AM/PM"),"")
&CHAR(10)&IF(COUNT(D23,E23)=2,
TEXT(D23,"h:mm AM/PM")
&" - "&TEXT(E23,"h:mm AM/PM"),""))

Format the cell to wrap text:

Format>Cells>Alignment tab>Wrap text>OK

You might also have to adjust the column width and row height to get the
proper display.

 

[srctr]

Thanks that worked great. I knew about the Char(10) and tried putting it in
but couldn't get it to work. I was missing the &

Thanks again

 

[T. Valko]

Very interesting site you have there!

 

[srctr]

That worked great. I knew about the char(10) but couldn't get it to work.
Had it in the right place but I was missing the & after it and before Text

Thanks for your help