woodland81
New Member
- Joined
- Dec 27, 2010
- Messages
- 14
Excel - Search Entries and compare dates in a range
<hr style="color:#ffffff; background-color:#ffffff" size="1"> I have a spreadsheet which Records Sickness for employees, there are filters so you can choose a person by surname or pay reference and it brings up how many entries have been made..relating to how many times been off sick. Other cells include dates.
Is this possible,
For it somehow it to flag up or alert you, if a person has been off X Amount of times, so look at unique Pay No (755793)and see how many entries its got, See when teh filter is on B4 totals how many times they been off so i know this field is key.
But it only counts if the person sickness is e.g 7times in a rolling year 1year. ive been playing around to try and work out how to do that, as when the filter is on, you will have to pick I think from the list and only include those that are 1year from =TODAY() as the sheet will hold sickness entries from previous years, but wont count.
I dont think its possible, as it seems to complicated to ask excel to check a range of dates then apply a rule, then IF etc do this.
Could be simpler if and still useful if just checked what pay ref had 7 or more entries. however longterm wouldnt be any good when some data will be years out of sync....hmm unless I get people to archive out any entries thats more than 1 year old.
On another version of this Sheet, Ive got a Button programmed, so when the curser is on a specific line, you press and it opens an email with already drafted info, relating to that person on that Line, extracting key info.
Way way ahead in an ideal world
Now if i can find a way to do the other Search and find who is guilty of the 7times off in rolling year, id combine it to give the User the option to automatically launch this email button to send.
Hope this makes sense, ive got plenty of theories to what I think but no great experience to do it
Lee
http://www.filedropper.com/sick
<hr style="color:#ffffff; background-color:#ffffff" size="1"> I have a spreadsheet which Records Sickness for employees, there are filters so you can choose a person by surname or pay reference and it brings up how many entries have been made..relating to how many times been off sick. Other cells include dates.
Is this possible,
For it somehow it to flag up or alert you, if a person has been off X Amount of times, so look at unique Pay No (755793)and see how many entries its got, See when teh filter is on B4 totals how many times they been off so i know this field is key.
But it only counts if the person sickness is e.g 7times in a rolling year 1year. ive been playing around to try and work out how to do that, as when the filter is on, you will have to pick I think from the list and only include those that are 1year from =TODAY() as the sheet will hold sickness entries from previous years, but wont count.
I dont think its possible, as it seems to complicated to ask excel to check a range of dates then apply a rule, then IF etc do this.
Could be simpler if and still useful if just checked what pay ref had 7 or more entries. however longterm wouldnt be any good when some data will be years out of sync....hmm unless I get people to archive out any entries thats more than 1 year old.
On another version of this Sheet, Ive got a Button programmed, so when the curser is on a specific line, you press and it opens an email with already drafted info, relating to that person on that Line, extracting key info.
Way way ahead in an ideal world
Now if i can find a way to do the other Search and find who is guilty of the 7times off in rolling year, id combine it to give the User the option to automatically launch this email button to send.
Hope this makes sense, ive got plenty of theories to what I think but no great experience to do it
Lee
http://www.filedropper.com/sick
