vba - Formula to automate scheduling tool Excel -


i have 1 worksheet lists every employees' start , end time each day of week. on worksheet have show data in colored bars greater how many people working each hour. insert "1" each of cells apply when employee should working. don't know if need combination of match,index,if statements. appreciated.

you need countifs formula.

on sheet 1, enter in employee names , work times, start , end times in separate columns. should have employees' names in column a, start time on monday in column b, end time monday in column c, start time tuesday in column d, etc. format start , end time cells times.

on sheet 2, create table days of week column headers starting in column b , skipping every other column in row 1 (monday in col b, tues in d, etc), , each hour of work day down column starting in row 2.

on sheet 2 enter formula in b2:

    =countifs('sheet1'!b$2:b$#ofemployees,"<="&$a2,'sheet1'!c$11:c$14,">="&$a3)

"#ofemployees" should not in formula. number of employees have. need enter in number here complete range.

sheet1 name of sheet employees' names , work times on.

drag formula down end of work day. select range b2 dragged down, , drag across end of week. in every other column, see number of people working hour.

select range b2 end of table. under home tab, go conditional formatting , select appropriate color scale.

on sheet 2, hide columns blank data in table (between days of week) if want clean bit.


Comments

Post a Comment

Popular posts from this blog

javascript - RequestAnimationFrame not working when exiting fullscreen switching space on Safari -

request animation frame stops working when exiting fullscreen clicking on original space's safari window. fine if fullscreen mode canceled escape key or calling cancelfullscreen(). steps reproduce: open https://dl.dropboxusercontent.com/u/769042/prezi/safari-fullscreen.html click "draw raf", kittie appears click "fullscreen", go fullscreen click "draw raf", kittie appears go space original safari was, showing "click exit fullscreen mode", click anywhere, out fullscreen click "draw raf", nothing happens what handling click calling: window.requestanimationframe(draw); which draws on canvas context: function draw() { ctx.drawimage(img, math.random()*500|0, math.random()*400|0, 100, 100); } i checked .hidden , .visibilitystate, updated correctly. tested on osx 10.9.3, safari 7.0.4 (9537.76.4). has workaround/solution other switching old settimeout? this sounds webkit bug 88940 : using reque
Read more

Python ctypes access violation with const pointer arguments -

i have api i'm trying wrap in python (2.7.6 on win7) code using ctypes. here's api: client_dllfunc bool clientapi search_exporttoclipcopy(clienthsearch handle, int channel, lpctstr filename, const time_t& from, const time_t& to, const bool* cameras, int length, bool usepassword, lpctstr password, bool includetextin = false, bool excludeplayer = false); the issue i'm having cameras argument; , i've had issue other const pointer arguments well. here's how i'm wrapping api: def search_exporttoclipcopy(self, hsearch, csearch, filename, tfrom, tto, cameras, length, usepassword, password, includetextin=true, excludeplayer=false): exporttoclipcopy = self.sdkdll.search_exporttoclipcopy exporttoclipcopy.argtypes = [c_int, c_int, c_wchar_p, c_long, c_long, pointer(c_bool), c_int, c_bool, c_wchar_p, c_bool, c_bool] exporttoclipcopy.restype = c_bool exporttoclipcopy(hsearch, csearch, filename, tfrom, tto, cameras, length, usepassword, pass
Read more