Help with my Staffing File

[Sicarii]

I've attached a snippet if this is hard to understand.

I have a grid setup in 15min intervals from 8:00am-9:00pm; represente
by D1:BC1. There is a row below for every employee. Each cell tha
they work is filled in with a "1".

I also have a "written" schedule tab where I actually write in th
times they work and have breaks.

Is there anyway to have this automatically fill in the written schedul
based on where the "1"s are filled in

Attachment filename: schedule.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55415

 

[Frank Kabel]

Hi
dou you also want to show breaks, lunch hours etc. If yes
are there some restrictions. e.g.
- max. of two breaks
- only one lunch time
- etc.

The starting and end time are easier to get. e.g. if you
want to get the time from the first '1' and the last '1'
for row 3 try the following array formula (entered with
CTRL+SHIFT+ENTER):
=TEXT(INDEX($A$1:$BC$1,MIN(IF($D$3:$BC$3=1,COLUMN
($D$3:$BC$3)))),"hh:mm") & " - " & TEXT(INDEX
($A$1:$BC$1,MAX(IF($D$3:$BC$3=1,COLUMN($D$3:$BC$3))))+TIME
(0,15,0),"hh:mm")

To get the lunch break use the following array formula
(assumption: only one lunchbreak):
=TEXT(INDEX($A$1:$BC$1,MIN(IF($D$3:$BC$3="L",COLUMN
($D$3:$BC$3)))),"hh:mm") & " - " & TEXT(INDEX
($A$1:$BC$1,MAX(IF($D$3:$BC$3="L",COLUMN($D$3:$BC$3))))
+TIME(0,15,0),"hh:mm")

To get the two working time frames before lunch and after
lunch use:
=TEXT(INDEX($A$1:$BC$1,MIN(IF($D$3:$BC$3=1,COLUMN
($D$3:$BC$3)))),"hh:mm") & " - " & TEXT(INDEX
($A$1:$BC$1,MIN(IF($D$3:$BC$3="L",COLUMN
($D$3:$BC$3)))),"hh:mm")

and
=TEXT(INDEX($A$1:$BC$1,MAX(IF($D$3:$BC$3="L",COLUMN
($D$3:$BC$3)))+1),"hh:mm") & " - " & TEXT(INDEX
($A$1:$BC$1,MAX(IF($D$3:$BC$3=1,COLUMN($D$3:$BC$3))))+TIME
(0,15,0),"hh:mm")

To get the first break time use (assumption: a break can
only last 15 minutes):
=TEXT(INDEX($A$1:$BC$1,MIN(IF($D$3:$BC$3="B",COLUMN
($D$3:$BC$3)))),"hh:mm") & " - " & TEXT(INDEX
($A$1:$BC$1,MIN(IF($D$3:$BC$3="B",COLUMN($D$3:$BC$3))))
+TIME(0,15,0),"hh:mm")

The second break:
=TEXT(INDEX($A$1:$BC$1,SMALL(IF($D$3:$BC$3="B",COLUMN
($D$3:$BC$3)),2)),"hh:mm") & " - " & TEXT(INDEX
($A$1:$BC$1,SMALL(IF($D$3:$BC$3="B",COLUMN($D$3:$BC$3)),2))
+TIME(0,15,0),"hh:mm")


All formulas are array formulas! Hope this helps getting
you started

 

[Sicarii]

Works like a charm dude, Thanx! You just made my job sooo much easier.
Since the company is too cheap to buy some workforce managemen
software.

I just have one more related question. I also have one of the 'grids
as 'OT' so I can track all overtime worked. I haven't played with i
yet, but if the OT square is filled at the beginning or end of shift
then the IN/OUT time formula won't be able to look for "1".

So basically, will it be possible for me to play round with this an
throw in like a if statement? Or if you know what I would want to do
drop me a hint, you don't have to just post all the code :cool

 

[Frank Kabel]

Hi
yes you could add an IF statement. something like
=IF(first_cell="OT","",other_formula)
also entered as array formula

 

[Sicarii]

One thing that I cannot figure out with this formula is that if 1:00 i
the end time it will be displayed as 13:00. All other times seem t
work correctly, even 1:15. Any help would be appreciated

 

[Lady Layla]

1:00 pm is 13:00



: One thing that I cannot figure out with this formula is that if 1:00 is
: the end time it will be displayed as 13:00. All other times seem to
: work correctly, even 1:15. Any help would be appreciated.
:
:
: ---
: Message posted
:

 

[Sicarii]

Actually the cell that has 1:00 can be AM or PM; it doesn't matter.
What has to be changed is the 12:45 cell, if that is changed to AM the
1:00 will show 1:00 instead of 13:00