Search code examples
excelfunctioncell

Excel Spreadsheet for Work

I would like to make a Function in vba and here is the pseudocode and concept that I need. If anyone can give me help on this you will recieve proper credit for it.

So we have a spreadsheet for the work schedule that has times in each column in row one like such

400 430 500 530 600 etc

These correspond to the time 4:00a, 4:30a, etc. and in Column A we have the names of the Employees, Each half hour that the employee works we fill a 0.5 and if they start on a 45 or 15 we fill it with a 0.25. Is there a way I can have a Function test each cell when the 0.5 start and end, like if the cells to the right and left have nothing in.

Edit: Here is a sample case for Sancho.s

Sample of Finished Product

Sorry that it is hard to see, the description to the right says "This person has a half and should not be counted as a split shift". The shifts display in Column U are:

  1. 5:15-12:30p
  2. 6:30-8:30p/10-12p
  3. 5:45-9a
  4. 5:45-11p
Solution

  • The formulas below (with the results they produce) refer to this image. The actions performed by each step (each formula) are possibly self-explanatory.

    enter image description here enter image description here

    Cell $V$4 : =MATCH(TRUE,INDEX(($B4:$T4<>0),0),0) -> 3
Cell $W$4 : =SUMPRODUCT(MAX(($B4:$T4<>"")*(COLUMN($B4:$T4))))-COLUMN($B4)+1 -> 17
Cell $X$4 : =OFFSET($B4,0,V4-1) -> 0.25
Cell $Y$4 : =OFFSET($B4,0,W4-1) -> 0.5
Cell $Z$4 : =OFFSET($B$3,0,V4-1) -> 5:00
Cell $AA$4 : =OFFSET($B$3,0,W4) -> 12:30
Cell $AB$4 : =IF(X4=0.5,TEXT(Z4,IF(MOD(V4,2)=1,"h","h:mm")),TEXT(Z4+TIME(0,15,0),"h:mm")) -> 5:15
Cell $AC$4 : =IF(Y4=0.5,TEXT(AA4,IF(MOD(W4,2)=0,"h","h:mm")),TEXT(AA4-TIME(0,15,0),"h:mm")) -> 12:30
Cell $AD$4 : =W4-V4+1-COUNT($B4:$T4) -> 0
Cell $AE$4 : =AD4>0 -> FALSE
Cell $AF$4 : =MATCH(TRUE,INDEX(ISBLANK(OFFSET($B4,0,V4-1):OFFSET($B4,0,W4-1)),0),0)+V4-2 -> #N/A
Cell $AG$4 : =AF4+AD4+1 -> #N/A
Cell $AH$4 : =OFFSET($B4,0,AF4-1) -> #N/A
Cell $AI$4 : =OFFSET($B4,0,AG4-1) -> #N/A
Cell $AJ$4 : =OFFSET($B$3,0,AF4) -> #N/A
Cell $AK$4 : =OFFSET($B$3,0,AG4-1) -> #N/A
Cell $AL$4 : =IF(AH4=0.5,TEXT(AJ4,IF(MOD(AF4,2)=0,"h","h:mm")),TEXT(AJ4-TIME(0,15,0),"h:mm")) -> #N/A
Cell $AM$4 : =IF(AI4=0.5,TEXT(AK4,IF(MOD(AG4,2)=1,"h","h:mm")),TEXT(AK4+TIME(0,15,0),"h:mm")) -> #N/A
Cell $AN$4 : =AB4&"-"&IF(AD4>1,AL4&"/"&AM4&"-","")&AC4 -> 5:15-12:30

    They may work for you.

    They give the number of the column (minus one, to avoid counting the column containing names) where data start/end. Formulas do not check the contents of cells having data.

    Cell B3 (as an example of row 3) contains =TIME(INT(B1/100),INT(MOD(B1,100)),0), and is formatted as Custom (different from C3). Anyway, row 3 is only for using in formulas, and it can be hidden. Row 1 is just for seeing more clearly the relative column number.

    Then you would concatenate with the last formula.

    And for adding a/p you would only use something like IF(<time> < TIME(12,0,0), "a","p") (<time> should be replaced by a reference to the cell containing the start/end time that you want -overall or split-). Then, concatenate as indicated above.

    You may keep all the cells used here (it's easier for working/debugging), or create one single formula altogether by combining others. But it will be extremely long (I'm not even sure if it will be longer than some limit from Excel). My suggestion is that, if possible at all, those cells are kept. You can hide them or, better, group them: select the columns, and Data -> (Outline) Group. You will see a box at the top that lets you collapse/expand the group.

    I guess you have everything you need.