Employee Calendar Calculation Problem

**jasono** · 09-14-2011, 11:01 AM

Have a question hoping to get some help with. Need someone smarter than me to figure this out.

I'm creating an attendance calendar for my employees where they can go in to view their amount of vacation days and other bonus days they are given.

I've got it figured out except for what we call and "Earned Day". An employee earns an Earned Day for 3 months of perfect attendance.

On my calendar for each month there is a row called "Marks". Here, as long as the employee does not have an L (Late), LE (Leave Early), NCNS (No Call No Show) within the 3 months he is eligible to get the Earned Day, which is noted as ED* in the Earned column.

For example, on the calendar I have attached, there is an ED* on cell S35 (which corresponds to date March 17th).

The code should look for the last ED* on the spreadsheet, and write ED* to cell S65 (which corresponds to June 17th) as long as there are no (L,LE,NCNS) between that time frame.

If there is an (L,LE,NCNS) in the columns, write ED* in the row which corresponds to 3 months after the date of the L,LE, NCNS).

I appreciate the help of anyone who attempts to tackle this one.

**Leith Ross** · 09-14-2011, 07:37 PM

Hello jasono,

You do manage to come up with some interesting challenges. The macro below will post the earned day on the calendar. The macro has been added to the attached workbook.

ThisWorkbook Module

Please Login or Register  to view this content.

**jasono** · 09-15-2011, 09:56 AM

Leith, can't thank you enough for this code! I learned a lot just be reading through it.. I found a small problem that maybe you can help me fix..

If I do an Earned Day calculation, the code writes an ED* perfectly like it should. However, if a couple of days later I write a L, NCNS etc, the ED* that was originally written is still there, and will put another ED* 3 months after that, and continue doing it.

I'm going to have the Earned Day macro run when the workbook is opened. How can I make the Earned Day macro clear all downstream ED* and recalculate? Is it possible to do it from the date?

I surely appreciated the help you have given me.

Here is your code:

Please Login or Register  to view this content.

**HenryCrun** · 09-15-2011, 10:21 AM

A problem with Leith's code is that it doesn't take into account different lengths of months. For instance, if the last ED* is placed 31st January, it tries to place the next ED* on the 31st April. How would you want this treated?

edit: Another thought

I see you have a space for "NEXT POTENTIAL EARNED DAY". Could the macro first calculate the next potential day and enter it in this cell, then only enter it in the main calender if the current date is equal to or greater than that date? That would solve the problem of lingering future ED*s which are no longer available.

e.g. Last ED* 17th March, 17th June is calculated and entered into the Next Potential Earned Day cell. If an L etc. is added on 17th April , the Next Potential Earned Day cell changes to 17th July but, if not, when the spreadsheet is opened on 17th June (or later), ED* is then entered into the main calender.

edit:

Here's what I mean

Please Login or Register  to view this content.

Note: I have named AF5:AG5 "NPED" for this macro. It also recalculates invalid dates to the next valid date (so 31st January --> 1st May rather than 31st April)

**Leith Ross** · 09-15-2011, 11:40 AM

Hello jasono,

Clearly, I did not fully understand what you wanted to do. Here is my list of questions about what the macro should do.

Will there be any existing "ED*" entries when the macro runs?
If there are, is the macro to clear them?
Will the macro check each quarter (3 months) for marks and insert "ED*" if the criteria are met?
Is a quarter designated as a 90 period starting with the first of the month?

**jasono** · 09-15-2011, 04:28 PM

HenryCrun, actually this would work even better. Would solve the next problem I was working on as well which was trying to calculate the next potential Earned Day.

I copied the code in and ran the macro, and it gave me a popup error with 400 in it. I looked that up, and it was recommended to put an error catch in which I did. The error I now get when I try to run it is:

Method 'Range" ofobject"_worksheet failed

What do I need to do in order to fix that?

Thanks both Henry and Leith for your help. I am learning a lot from your assistance.