Excell Formula

idontknow
September 14, 2008 at 17:20:06
Specs: idontknow, idontknow
I am trying to write an excel formula for my work to easily track excessive absence's.
I think this would be it...

If the date inserted in the cell is over thirty days old it will be deleted unless there is at least one date already listed from that same month. However any cell containing a date that is in a month two previous from the current date is deleted without exception. Also there will be a list of names under the A colum that need not be altered.

This comes from our hand book which states, "
Excessive absence is defined as 3 or more occurrences of being late or absent in a 30-day period, or, being absent two or
more days in a month for more than two months, demonstrating a pattern of absenteeism."


See More: Excell Formula

Report •


#1
September 14, 2008 at 19:06:52
How are these dates laid out in the spreadheet? If we're going to write formulae or macro to compare dates, I think we need to know a little about the layout of the spreadsheet.

Unless, of course, you are starting from scratch and are looking for a complete solution.


Report •

#2
September 14, 2008 at 19:19:43
This is from scratch however the dates will be entered in by a member of management. I assume some part of a program is already at place in the excel spread sheet because for example if I enter in the following 8/14 it will automatically convert that to 14-Aug. Additionally if I click on that cell it will show me 8/14/2008 on the Fx line.
Excel Example with out automatic deletion
A B C D
1. John 14-Aug 21-Aug 16-Sep
2. Tim 12-Sep 19-Jul 21-Sep 25-Sep
3. Bob 12-Apr 8-Jul

Report •

#3
September 15, 2008 at 09:08:12
re: I assume some part of a program is already at place

What are you are seeing has nothing to do with your attendance project. It is nothing more than the defaults that are set within Excel for how dates are displayed. Try it in a new workbook and you will see the same behavior.

re: automatic deletion

Excel cannot delete the contents of another cell via a formula. A formula in one cell cannot perform an action on another cell. All they can do is display a result (or error) in the cell that the formula is entered in.

It might be possible to write some VBA code (a macro) to compare the dates for each employee, but you would need to have someone comfortable with running and maintaining VBA code.

Any macro written to do this would be heavily dependent on the layout of the spreadsheet and how data is entered. For example, I see that the entries for Tim are not in chronological order. Is there a reason for that?


Report •

Related Solutions


Ask Question