Working Dates and Off Dates Formula

See attached workbook sheet 5 Can someone help me with a formula for column C that will display "Worked" "Off" or Blank ""

Dates are B7:B371 to check if they are Between D7:D371 & E7:E371 = Worked or F7:F371 & G7&G371 = Off and if date is not with either ""

Has to be a formula this is being done on MAC Excel 2008 so no macros

Try...
=IF(AND(B7>=D7,B7<=E7),"Worked",IF(AND(B7>=F7,B7<=G7),"Off",""))
in C7 filled down to C371

Currently all rows return "" because all column B dates are earlier than corresponding column D dates.

Beau Nydal

Not sure if you get what I need

I need row A dates to check all the dates between D&E and F&G I know confusing have been trying to figure it out for a while that why i'm asking for help I'm not sure but reading may need a array formula or a lookup.

I want this workbook to figure out any schedule days on or off and allow you to see the hours per year or and days work and days off and then will fill calendars to show who is on and off

So hopefully someone can help me with this road block

Here is what finally worked


=ArrayFormula(if(sumproduct(B6>=D$6:D,B6<=E$6:E),"worked",if(sumproduct(B6>=F$6:F,B6<=G$6:G),"OFF","")))

Originally Posted by jettechfsr

Here is what finally worked


=ArrayFormula(if(sumproduct(B6>=D$6:D,B6<=E$6:E),"worked",if(sumproduct(B6>=F$6:F,B6<=G$6:G),"OFF","")))

That just produced the NAME error for me.
This array formula however resulted in a blank in C6 and either Worked or Off in the other column cells...

Please Login or Register  to view this content.

Beau Nydal

Thanks Beau I see now the difference between google Doc's and Excel so when I export will have to use your formula