attendance log

[excellionaire]

Question:

I am trying to make an attendance log.

I need it to have a continuous watch over a 365 day period, where if more than 7 sick days were taken by any employee over a 365 day period, a pop up or some kind of message notifies me.

Any and all help would be greatly appreciated.

i am trying to figure it out but kind figure out the logic.

Please if you need more information dont hesitate to ask,

[MarvinP]

Hi excellionaire and welcome to the forum.

See the attached with how I'd do this problem.
It uses a list of employees to fill in their names. The conditonal formatting will allert you if they have more than 365 days sick using Today - 365. See the Formula in the calculation of days sick.

[excellionaire]

thank you soo much...

here is my problem now though...

with your program:

I need it to strictly count all sickdays within a 365 year period. once it pasts the 365 days mark then the employee is entitled to more sickdays that fall off.. your program keeps counting even if the sick days are over a year old.


I really appreciate what u have done though, i will try to make it adabt.

thank you again.

[brokenbiscuits]

No, Marvin's formulas look only for dates that fall in the last 365 days. So although older sicknesses might show on the spreadsheet, it won't count against the employee.

[excellionaire]

you are right, i am just an idiot.

TY


I changed the code from -365 to -366 in all instances, in order to better encapsulate, july 7 10 to july 7 11 for example...it wouldnt count those dates if it were 365.

[excellionaire]

can anyone help me orient that code so its horizontal, ...
IE like this template. just Q1 i will change so its like marvins code...perpetual, not sorted by quarters.

[excellionaire]

okay i've managed to make it a sideways scroller.

Basicly, i have a company roster along the left column.
I have the spreadsheet set to sticky after the days late col
so i can side scroll without forgetting who I am working on.

here is my new problem. I dont want to be constantly scrolling back and forth to edit/add cells.
There a way where i can click on the persons name and it will go to the first blank entry for that person?


again, i appreciate all your help.

or is there a button that will know which line im on and go to the last sideways entry for that line?

[MarvinP]

Hi excellionaire,

You could do with an Event Macro to solve this one. Double Click on a name in column A and this code will take you to the last entry in that row.

Please Login or Register  to view this content.

See the attached for the samele.

[excellionaire]

Marvin you magnificent Bastage!!
i love you!

call me an idiot when i dont see things like your attached files...

PLEASE!

[excellionaire]

marvin love

[MarvinP]

I"m not used to all this emotion

I find it fun to have problems that might be solved. No politics, religion or ethics.

I'm glad you liked the possible solution.

[excellionaire]

one last question sir, i hope you dont mind.


okay:

so basicly i need dates that are over 365 days to start falling off.
and then shift the column over...this possible?

please see the attached file to see my layout.

i am probably going to remove the total sick days column.

[MarvinP]

Hi,
I think I'd leave those old dates in as you may need them for something in the future.
I'm a little worried as the dates from left to right are not in order. What if you have an old date in Column
I or J and you removed them.

I think you are looking for some VBA code to look at each date and delete the cell and shift all the others to the left one cell. I don't like deleting stuff as you never know when you may need it in the future.

[excellionaire]

the dates will fall in order, thats what the boss wants...

but i understand about the vba code thing. Im assured that it will be ordered correctly though if that makes things easier.

[MarvinP]

OK,
Find the attached that has code to look down column D and delete all the dates older than today -365.

The code can be improved but I'm just seeing if you can understand it.

[excellionaire]

tytyty


omg boss is killing me

I cant even think of a way of doing this.

any way we can somehow include time into the equation. Incase someone takes a half day off work.

[excellionaire]

any help would be appreciated

[excellionaire]

Is there a way i can get the Sick Last 365 not to include anything that i may highlight, and for those highlighted dates to be counted in on Sick Dependent.???


Thank you again.

[excellionaire]

so ive managed to implement this code:

Function CountColor(Rng As Range, RngColor As Range) As Integer
Dim Cll As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each Cll In Rng
If Cll.Interior.Color = Clr Then
CountColor = CountColor + 1
' Worksheet.Calculate
End If

Next Cll

End Function


into it, and after doing some research i found out that changing background color of a cell doesn't trigger an event. Only way for updates to be noted is by manualy selecting the cell and clicking out of it to update it.

So I am now trying to implement the Worksheet_SelectionChange function into this, but im not sure how to do this..

[excellionaire]

okay so i gave up on the highlighting thing...

Instead i decided to denote Sick Dependants with an ! mark in the end. One problem.

The Automatic Delete button wont detect dates with ! marks.
is there any way i can get this to work?

sorry for all the questions.

[excellionaire]

can anyone help?