Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Leave record (MS Excel 2000)

    Hi,

    I would like to keep a record of the annual leave applied for and sanctioned and also annual leave availed by my staff and this should be shown in an excel sheet on monthly basis. This will help me in checking the leave over lap of the staff and better leave management of the staff. To this I would also like to add the sick leave record. As half day ie AM or PM leave can also be availed I would like this feature also in the worksheet. Finally the Worksheet summary should be able to give me the leave due to an employee, leave sanctioned, leave availed for the Annual leave and leave due and leave availed for the sick leave of each employee. I am sure someone must have made this excel programme (Lotus Organiser had something close to this) as my requirements are quite ordinary. As I am looking for a free software / programme can somebody help me to locate the software/programme.
    Merry Xmas and and Happy New Year to all the Gurus.
    Cheers

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave record (MS Excel 2000)

    Perhaps the Employee absence schedule or Employee absence tracker template from Microsoft can be adapted to your needs.

    Have you tried a Google search?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leave record (MS Excel 2000)

    Hi Hans,

    I have amended one of the above-proposed files to suit my requirement. However, I require help to solve one problem. In the monthly chart each box represents half days leave viz AM or PM. The user should only shade the leave period, say Red for Annual leave and Yellow for Sick leave and this should automatically get reflected in Summary sheet. I am aware of the sum function for numerals across a range of sheets but I do not know how the excel can read a color in the box and convert same to numeral for the summary sheet. Can you pls help me to find a solution to this problem.
    Rgds
    Novicexcelite

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave record (MS Excel 2000)

    Excel does not have built-in functions to count cells by color. You can find custeom functions on Functions For Working With Cell Colors on Chip Pearson's site.

    In the attached workbook, I have used this function in a few cells in the summary sheet (D10:E11 only) as an example.

    Note 1: formulas using the CountByColor function will only be updated when the worksheet is recalculated. If necessary, press F9 to recalculate.
    Note 2: in the default color scheme, red = 3 and yellow = 6.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leave record (MS Excel 2000)

    Hans,
    Thanks for the help. there seems to be sum bug. In your attached file if I add another half day leave in the month of January the result is 11 days and not 10.5 days. Can u reslove this issue also.
    Regds
    Novicexcelite

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave record (MS Excel 2000)

    The problem is that the columns are too narrow to display 10.5, so the displayed value is rounded. If you make the columns slightly wider, you will see that the cell actually contains 10.5.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Leave record (MS Excel 2000)

    Another option to avoid the color function issue altogether:
    Have the employess just enter a something into the cell (eg "X" or even a space), then you could then just use the counta function to count the non-blanks in each of the rows. It wouldn't matter what they enter.

    If you wanted them colored, you could add cond formatting in the month charts to check for it being non-blank and whether col C had an A or an S to indicate the color

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leave record (MS Excel 2000)

    Hi Hans,

    Thanks for the tip. i should have seen it myself. Now i am attaching the file again would like to know how to remove the zeroes in the summary sheet. Whenever the person avails leave and it is reflected in the monthly chart, the summary sheet will indicate the amount and in the appropriate background colour. However, when the person does not take leave I do not want the zero to show up as this unnecessarily clutters the summary sheet.

    As regards the second gurus tip regarding not using colours altogether, i am not for it as I want the different leave in distinct colours. Anyway thanks for the tip.

    Rgds
    Novicexcelite

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Leave record (MS Excel 2000)

    In tools- options - view(tab) uncheck "Zero values"
    to remove display of all zero values from the book

    Or just in this range, select the range and format - cells - custom
    0.0;-0.0;""

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave record (MS Excel 2000)

    1. You can set the number format for the cells to a custom format that hides zero values, for example <code>General;;</code>
    (this format will hide negative values too).

    2. Steve didn't suggest NOT using colors at all, but to let the user enter an "x" or something like that, and using conditional formatting to change the background color of the cells according to the type of leave. The advantages are
    - Data entry is much simpler
    - Your workbook does not need to contain VBA code.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leave record (MS Excel 2000)

    Hi Steve,

    Thanks a lot for your tip. Now I have got the product that I wanted. However, I wonder why the file at the time of opening gives a popup for a macro whereas we have not recorded any macro but only Formulas and conditional formatting.
    Rgds
    Novicexcelite

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave record (MS Excel 2000)

    Does your template/workbook still contain the custom function CountByColor? That would account for the macro warning.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Leave record (MS Excel 2000)

    In addition to Hans' suggestion about the macro code, even if you have deleted the code itself, the module must also be removed (right-click - remove module). Just having a module (even an empty module) will trigger the macro warning.

    Steve

  14. #14
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leave record (MS Excel 2000)

    Hi Hans & Steve,

    Understood the reason for the macro confirmation as the formula countbycolour is being used in the workbook. Your suggestion about entering "A" for annual leave and "s" for sick leave and then changing the colour by conditin formatting is definitely a good one. But how does one do condition formatting in this case. Pls help
    Rgds
    Novicexcelite

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave record (MS Excel 2000)

    Select a range of cells to be formatted, for example 'Jan 2005'!H6:Q49
    Select Format | Conditional Formatting...
    Set "Cell Value Is", "Equal To", A.
    Click Format...
    Activate the Patterns tab.
    Click on red, then OK.
    Click Add>>.
    Set "Cell Value Is", "Equal To", S.
    Click Format...
    Activate the Patterns tab.
    Click on yellow, then OK.
    Click OK again.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •