Conditional Formatting Problem 3

[Budster13]

I wrote a time card program in Excel 2000 (originally in XL97) that has been working now for two years. I have just discovered that there is a flaw in the sick day accumulation portion. Here's the way it's supposed to work. Our civilian employees can accumulate up to 45 sick days at a rate of 9 days per year and they receive those sick days on their anniversary date. I've got that covered pretty well, but the problem occurs when the employee has reached the 45 sick day limit and uses some sick days before their anniversary date and some after.

For example, the employee has 45 sick days accumulated at the beginning of the year and two sick days were taken in March (which is before the anniversary date of 10/24/02) and then another two sick days were taken in December. Since this employee received another 9 sick days on his anniversary date (but can only accumulate up to 45 days), the actual total accumulated should be 43 days (maximum of 45 on his anniversary date, then 2 taken off after the anniversary date), but the spreadsheet see all of the sick days for the entire year and deducts a total of 4 sick days thus reports a net of 41 days.

I need a formula that says if the employee has 45 accumulated sick days and has not used more than 9 days before his anniversary date, delete the sick days used prior to the anniversary date and start over from the anniversary date until the end of the year. The program works fine if the employee has less than the maximum of 45 days. Somehow, I need the spreadsheet to recognize the anniversary date and compare it to today’s date, then change the total based on the calendar date and sick days taken off.

 

[Zathras]

Can you post some sample data please. It's a little hard to visualize where the various bits are going to come from.

 

[HobbitK]

Budster .. I am a VB programer and I really only dabble in VBA, but I will try to work something out for you in VB that you can use as a guide. I think I understand what you want, and I dont think it should be that difficult.
Will try to have this done by this afternoon, but that depends on my clients demands for today.
Michael

 

[bandit600]

If both todays date and the anniversary date appear on the sheet, you could incorporate an If statement in your formula along the lines of

If(todaysdatecell>=anniversarydatecell,deduct2,deduct4)

I hope I haven't oversimplified the problem.

bandit600

 

[PeterMoran]

Hi Budster13,

I am guessing a little here, but it seems to me that your processing might be simpler if you said the following:

At anniversary date take off the sick days and add the nine days. If the new total is > 45 set it to 45. Subsequently the sick days since the anniversary just amend the total.

Further thinking:
Is part of your problem that you have to keep the sick days for the calendar year, and although you apply the new days at the anniversary, the sick days are only applied at calendar year end?

In this case would it be advantageous to maintain totals of sickdays before anniversary and sick days after anniversary?

If you currently don't have dates against each sick day and the anniversary, it would seem that you need to update the total when each event occurs. If you carry forward the sick days you have no way of knowing subsequently whether they occurred before or after the anniversary.

We probably need finer details at thie stage to be able to assist further.

Good Luck.

Peter Moran
Two heads are always better than one!!

 

[DaleWatson123321]

Budster13,

Given that your time card program has been in use for two years, I suspect you might be required to maintain the basic layout of the time cards, especially if the electronic version was purposely designed to duplicate the manual version.

If the time card structure is designed after a typical "physical" time card, it’s possible that your Excel version is therefore NOT in a type of "database" structure. In order for assessment of the data, to determine the number of sick days used prior to and post anniversary date, there needs to be a database structure established.

I believe it shouldn’t be difficult to set up a database by creating formulas that reference the cells of your current time card. Then using Excel’s powerful "database functions", it should be relatively easy to isolate the sick days used prior to and post the employee’s anniversary date.

In addition, once the database has been established and you see how effective the database formulas are, this will possibly "trigger" other ideas – for example isolating other data that can be gleaned from your new database.

In your title, you mention Conditional Formatting. I expect it should be possible to conditionally format the calendar, to automatically display a different color to denote the start of each pay period.

Because your time card system is likely to be one that you need to change from year to year to reflect the obvious changes in the calendar months of each new year, I feel I can contribute significantly in automating the transition to each new year. I say this because of other programs I’ve developed where all the user has to do to convert to the new year, is to simply enter one number in one cell – the number of course being the number of the new year. This will then trigger the change of all the calendar and pay period dates.

If you’d like to email me your existing model, I’ll be pleased to help you out. Unless you’ve had experience in using Excel’s database formulas, you’ll likely need help. This is due in part to sub-standard documentation and examples by Microsoft.

I hope this can be an option.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Budster13]

Thanks to all of you who have responded to my question. You all have some really good ideas, but DaleWatson123321 seems to have hit the home run so to speak. I will email the program to you and we can begin to analyze the best approach. Budster13