Posts by kennethbrandon

    Good day!


    I would be very happy if you could give me some solutions to the attachment I forward here.

    The problem is that when "TIME END" at column E is 2:00 PM the "Zero" Values occured (circle in Red) which I couldn't get rid of. I have used Conditional Formating and Format Cell, it doesn't work too. May Ihave some help please. Thank you very much!

    Goodday Sir,


    Thank you very much for your feedback which I find I am almost solving my Absentees Worksheet.


    I have applied the VBA Code but I feel very sorry I can’t make it working. Once again I really hope you could


    Help me to see to my worksheet where the absentees on the specific date are to be placed in column H accordingly.


    Thanks again . God bless!

    Files

    Greeting Sir,

    Thank you for replying my attachment. Once again I attached my work sheet for you to see.

    I have tried using Unique Name List and V Lookup but it does'nt show the correct result.

    Thank you very much for your kind help.

    1. COLUMN B is a range of Staffs name List with 40 staffs.

    2. COLUMN C is a range of daily Attendance based on the Time and Date at COLUMN D : G

    3. What Formula do we use to determind the daily ABSENTEES at COLUMN "H" based on the specific Date from CLOUMN ''G" ?

    Files

    Hello,


    I am looking for someone helping me to solve my attendance sheet where I can't get the Absentees based on the aqttendance date.

    I have tried using vlookup but it is also very upsetting.


    Attached here with my worksheet.


    I would be very pleased and would appreciate your help. Thank you so much.

    Files

    • ABSENTEES.xlsx

      (13.57 kB, downloaded 100 times, last: )

    Hello again,


    Very sorry for my poor questions. Here I am doing the corrections.
    I would like to know what formula is to be used in the columns "PUBLIC HOLIDAY" for calculating hours of day duty and night duty in column K16:L23. Working hours are as follows:
    7am to 2pm : morning duty
    2pm to 9pm: afternoon duty
    9pm to 7 & 8am night duty but night overtime calculates from 10pm to 6am.
    Thank you very much.

    Hello Sir,


    In this worksheet, I would like to fill in the data in column G17:L23 from
    B17:E23. If anybody could help me to get the formula in G17:L23.
    Your help is very much appreciated. Thank you.

    Files

    • OVERTIME.xlsm

      (16.84 kB, downloaded 96 times, last: )

    Greetings to you,


    I wish to transfer my data from sheet 1 (MON) to class Time Table (2G). I have tried using several formulas such as VLOOKUP, CONCATENATE and several more with my worksheet but I couldn't get the correct result.
    Please help me.

    Files

    • TIMETABLE.xls

      (45.57 kB, downloaded 105 times, last: )

    Re: 2 groups conditional formating



    Hi Sir,
    I have applied the formula into my worksheet but all the datas were highlighted as below.
    What shoul I do next Sir?


    No Staff Names Posts Date Time In
    [TABLE="width: 633"]

    [tr]


    [td]

    1

    [/td]


    [td]

    BERDAH ANAK RUNIES

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:38:57 AM

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    CHRISTINA CHIA

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:39:15 AM

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    PAUL YEO

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:40:22 AM

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    AMY NANCY ANAK MICHAEL AE

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:40:49 AM

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    TAROT ANAK AGEH

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:41:23 AM

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    ELZA ANAK LINGKONG

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:41:40 AM

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    THOMAS SULONG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:41:56 AM

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    [email protected] ANAK GLAI

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:42:23 AM

    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    RASIT BIN ABAN

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:43:26 AM

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    NIEW ANAK JUPONG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:44:05 AM

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    HASZAMAN BIN SIRIEE

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:45:43 AM

    [/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    MERIAH BINTI JULY

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:46:10 AM

    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]

    JERRY AK AGIT

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:46:31 AM

    [/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    ROLLAND LINGGAT

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:46:51 AM

    [/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    SUNAINI BIN USIN

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:47:24 AM

    [/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    NUR JULIANA ABDULLAH

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:50:22 AM

    [/td]


    [/tr]


    [tr]


    [td]

    17

    [/td]


    [td]

    GLADYS MINDENG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:51:28 AM

    [/td]


    [/tr]


    [tr]


    [td]

    18

    [/td]


    [td]

    SUUD BIN DENA

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:52:12 AM

    [/td]


    [/tr]


    [tr]


    [td]

    19

    [/td]


    [td]

    JINDU ANAK LUNDU

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:53:15 AM

    [/td]


    [/tr]


    [tr]


    [td]

    20

    [/td]


    [td]

    SINIMA ANAK GIHANG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:54:45 AM

    [/td]


    [/tr]


    [tr]


    [td]

    21

    [/td]


    [td]

    DONG YAL

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    6:57:22 AM

    [/td]


    [/tr]


    [tr]


    [td]

    22

    [/td]


    [td]

    PAUL JOSEPH

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:00:14 AM

    [/td]


    [/tr]


    [tr]


    [td]

    23

    [/td]


    [td]

    NANING ANAK AMBA

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:00:26 AM

    [/td]


    [/tr]


    [tr]


    [td]

    24

    [/td]


    [td]

    RASHIDAH BINTI BOL

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:01:34 AM

    [/td]


    [/tr]


    [tr]


    [td]

    25

    [/td]


    [td]

    DIZON ANAK GEMBANG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:01:56 AM

    [/td]


    [/tr]


    [tr]


    [td]

    26

    [/td]


    [td]

    SITI AZIRA BINTI JOSE

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:02:15 AM

    [/td]


    [/tr]


    [tr]


    [td]

    27

    [/td]


    [td]

    JUNTIA ANAK CLIVE JUSAK

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:02:52 AM

    [/td]


    [/tr]


    [tr]


    [td]

    28

    [/td]


    [td]

    NOR HASIKIN BINTI NONG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:06:54 AM

    [/td]


    [/tr]


    [tr]


    [td]

    29

    [/td]


    [td]

    KALANA BIN MU

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:07:40 AM

    [/td]


    [/tr]


    [tr]


    [td]

    30

    [/td]


    [td]

    PATRICIA ANAK LAYAS

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:08:09 AM

    [/td]


    [/tr]


    [tr]


    [td]

    31

    [/td]


    [td]

    NORHAIDA BINTI RAMBLI

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:10:24 AM

    [/td]


    [/tr]


    [tr]


    [td]

    32

    [/td]


    [td]

    MUNANG ANAK JAOH

    [/td]


    [td]

    AKS

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    7:43:40 AM

    [/td]


    [/tr]


    [tr]


    [td]

    33

    [/td]


    [td]

    PIOT EDWARD NAWANG

    [/td]


    [td]

    GURU

    [/td]


    [td]

    Tue 18-04-17

    [/td]


    [td]

    11:01:05 AM

    [/td]


    [/tr]


    [/TABLE]



    Hello ,
    I have two groups of staff, Admin Staff and Teaching Staff.
    I would Like to highlight the Staff Attendance that is not punctual
    The time for Admin Staff should not later than 8.00am where as for the Teaching Staff should not be later than 7.15am.
    How do I use conditional formatting so that I could highlighted the staff that is late as shown in the Example below. Thank you very much for your help.
    Example
    [TABLE="width: 392"]

    [tr]


    [td]

    StaffName

    [/td]


    [td]

    Group

    [/td]


    [td]

    Timein

    [/td]


    [/tr]


    [tr]


    [td]

    Steve

    [/td]


    [td]

    Admin

    [/td]


    [td]

    8.05AM

    [/td]


    [/tr]


    [tr]


    [td]

    Rolland

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    7.00AM

    [/td]


    [/tr]


    [tr]


    [td]

    Rosalind

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    7.16AM

    [/td]


    [/tr]


    [tr]


    [td]

    Jannet

    [/td]


    [td]

    Admin

    [/td]


    [td]

    8.20AM

    [/td]


    [/tr]


    [tr]


    [td]

    Decan

    [/td]


    [td]

    Admin

    [/td]


    [td]

    8.00AM

    [/td]


    [/tr]


    [tr]


    [td]

    Lulu

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    7.18AM

    [/td]


    [/tr]


    [tr]


    [td]

    Hanna

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    6.55AM

    [/td]


    [/tr]


    [tr]


    [td]

    Collin

    [/td]


    [td]

    Teacher

    [/td]


    [td]

    6.50AM

    [/td]


    [/tr]


    [/TABLE]

    [/SIZE][TABLE="width: 262"]

    [tr]


    [td]

    [TABLE="width: 392"]

    [tr]


    [td]

    [TABLE="width: 392"]

    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td][/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]

    Hello ..
    In my worksheet attached I am having some difficulties to get through the problems . ie: Cells X5:X42 are colums for stating the Reasaon of the absentees from column W5:W42. I would like to know what formula should I use so that every "reason" stated at colums X runs together with
    List Box "Q2"
    Thank you for your great help![/FONT][/SIZE]

    Files

    • Attendance.xlsm

      (629 kB, downloaded 105 times, last: )

    Re: Transfering data


    Hello Sir,
    Thank you very much, sir. It works great! Your formula is very fantastic!
    Could you please share with me how to copy another next new months to sheet 3,
    sheet 4 an so forth . Honestly, I am very new to VBA . Your help is very much appreciated, Sir. Thank you.

    Greetings Sir,


    I have a simple attandence sheet which I could not find the correct formula to use.
    I need to transfer The New Month Data to Sheet 2 by leaving the Cells empty.
    Attached here with my worksheet. Please Help me, Thank You.

    AUTO NUMBERING
    Greetings,
    I am using this formula at B4:B17 :-[IF(C4>0,MOD(ROW(C4)-4,"8")+1,"")] for the numbering as shown. I have a problem as the numbers at column B doesn't match with the dates where No. 1 should start as at date 15-3-17 (D10) . Please help. Thank you.

    Files

    • NUMBERING.xlsx

      (11.98 kB, downloaded 104 times, last: )