Hello, Please help. Please see link below.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
<o>http://uploading.com/files/mdcca5f7/test1.xlsm/</o>
<o></o>
I would like to incorporate our Absentism program into this spreadsheet. The Absentism program is designed to notify employees of their absences from work due to calling in sick. When certain criteria is met the employee is given letters informing him/her of where they are in the program.
I have 2 columns in my spreadsheet, the first column has a ISSUE DATE, the second column has a LETTER TYPE. The letter types can be 1. Advisory, 2. Councel, 3. Coach, 4. 2nd Coach, 5. Final Coach, 6. Termination.
This spreadsheet is used to track daily attendance. I would like a macro to prompt the user to issue another letter when specific criteria is met.
The criteria is 3 occurrences totaling 4 days or more over a 6 month period.
An occurrence is each time an employee is coded as sick, if an employee is sick consecutively, then this still counts as 1 occurrence.
e.g. employee is sick Thursday, Friday, Monday. This is 1 occurrence.
If the employee is sick Monday, then Tuesday then Friday, this would be 3 occurrences as it is not consecutive.
My spreadsheet tracks attendance from Jan to Dec, it uses a S or SU to track sick days. What I want done is this,
If the criteria is met, then I want the system to view the Letter type that this person is currently on and prompt the user to issue the next letter.
e.g. The ISSUE DATE is Jan 10th 2011, if an employee is sick Jan 18, Feb 17,18 & Mar 10, ( 3 occurrences totaling 4 days or more over a 6 month period.) then on Mar 10 when the user enters S or SU on the spreadsheet I would like the system to prompt the user to issue the next letter, let’s say the Letter Type on Jan 10th was Advisory, then the message on Mar 10th would say that this employee needs to be given a Councel letter. I would also like to get the date of March 10th highlighted in pink, so that the user is aware that on that particular date a letter was supposed to be issued. At this point the user issues another letter and changes the Issue date to Mar 10 and the loop continues.
If the criteria is not met, then I want the system to view the Letter Type that this person is currently on and promt the user to take the letter of the person's file.
e.g. The date is Jan 10th 2011, the employee is sick on Jan 18, Feb 17 and Mar 10, the criteria was not met, we had 3 occurences but only 3 days. At this point on June 10th 2011 ( 6 months from the Date of Jan 10th ) when the user enters this person's attendance, I need the system to promt the user to take whatever letter the employee is on of his file. I would also like the date of June 10th highlighted in green so that the user can identify when the letter was taken off. If June 10th happens to be a weekend, let's say a Saturday and no data is entered for that person then the first date following June 10th that data is entered is used to alert the user. In addition I want the user to be promted to set the Issue date to Jan 18th. The reason for this is because this person did not meet the conditions for receiving a letter, so his Issue date needs to be changed to the first day he was sick following the last issue date, then the process will repeat itself from Jan 18th.<o></o>
Keep in mind that the macro will have to be able to carry over to next years Attendance sheet, sheet 1 of the spreadsheet is called Jan to Dec 2011, I will make sheet 2, Jan to Dec 2012.<o></o>
e.g. If the issue date is Nov 16th 2011, then 6 months from that date will be May 16th 2012. If this person already had 2 occurrences in 2011 from Nov 16th to Dec 31st and the 3rd occurrence in 2012 meeting the criteria, then in 2012 the macro would run the above command. If the conditions was not met then on May 16th 2012 the macro will alert the user.<o></o>
<o></o>
Thx<o></o>
Neo<o></o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o><o
></o> <o
>http://uploading.com/files/mdcca5f7/test1.xlsm/</o><o
></o>I would like to incorporate our Absentism program into this spreadsheet. The Absentism program is designed to notify employees of their absences from work due to calling in sick. When certain criteria is met the employee is given letters informing him/her of where they are in the program.
I have 2 columns in my spreadsheet, the first column has a ISSUE DATE, the second column has a LETTER TYPE. The letter types can be 1. Advisory, 2. Councel, 3. Coach, 4. 2nd Coach, 5. Final Coach, 6. Termination.
This spreadsheet is used to track daily attendance. I would like a macro to prompt the user to issue another letter when specific criteria is met.
The criteria is 3 occurrences totaling 4 days or more over a 6 month period.
An occurrence is each time an employee is coded as sick, if an employee is sick consecutively, then this still counts as 1 occurrence.
e.g. employee is sick Thursday, Friday, Monday. This is 1 occurrence.
If the employee is sick Monday, then Tuesday then Friday, this would be 3 occurrences as it is not consecutive.
My spreadsheet tracks attendance from Jan to Dec, it uses a S or SU to track sick days. What I want done is this,
If the criteria is met, then I want the system to view the Letter type that this person is currently on and prompt the user to issue the next letter.
e.g. The ISSUE DATE is Jan 10th 2011, if an employee is sick Jan 18, Feb 17,18 & Mar 10, ( 3 occurrences totaling 4 days or more over a 6 month period.) then on Mar 10 when the user enters S or SU on the spreadsheet I would like the system to prompt the user to issue the next letter, let’s say the Letter Type on Jan 10th was Advisory, then the message on Mar 10th would say that this employee needs to be given a Councel letter. I would also like to get the date of March 10th highlighted in pink, so that the user is aware that on that particular date a letter was supposed to be issued. At this point the user issues another letter and changes the Issue date to Mar 10 and the loop continues.
If the criteria is not met, then I want the system to view the Letter Type that this person is currently on and promt the user to take the letter of the person's file.
e.g. The date is Jan 10th 2011, the employee is sick on Jan 18, Feb 17 and Mar 10, the criteria was not met, we had 3 occurences but only 3 days. At this point on June 10th 2011 ( 6 months from the Date of Jan 10th ) when the user enters this person's attendance, I need the system to promt the user to take whatever letter the employee is on of his file. I would also like the date of June 10th highlighted in green so that the user can identify when the letter was taken off. If June 10th happens to be a weekend, let's say a Saturday and no data is entered for that person then the first date following June 10th that data is entered is used to alert the user. In addition I want the user to be promted to set the Issue date to Jan 18th. The reason for this is because this person did not meet the conditions for receiving a letter, so his Issue date needs to be changed to the first day he was sick following the last issue date, then the process will repeat itself from Jan 18th.<o
></o>Keep in mind that the macro will have to be able to carry over to next years Attendance sheet, sheet 1 of the spreadsheet is called Jan to Dec 2011, I will make sheet 2, Jan to Dec 2012.<o
></o>e.g. If the issue date is Nov 16th 2011, then 6 months from that date will be May 16th 2012. If this person already had 2 occurrences in 2011 from Nov 16th to Dec 31st and the 3rd occurrence in 2012 meeting the criteria, then in 2012 the macro would run the above command. If the conditions was not met then on May 16th 2012 the macro will alert the user.<o
></o><o
></o>Thx<o
></o>Neo<o
></o>
Last edited by a moderator:
