I was working on a project last week where the person wanted on their staff
attendance where they wanted the sick days in the vacation days to disappear
after a year so that they could track only the stuff that was current so what
I've done is I'll show you how I did it now I declared I've done this so that
when it's opens it runs and I declare some values range l range column number
required date as date last date as long or last as long
f date is long X's integer now required date equals now it was designed to run
on 365 days - 365 days but because I'm trying to
display to you I'm doing only 30 so required a equals date which will
automatically should give us today - 30 and half date what this is is I am
converting the date the the required date back to the number form so that
it's gonna it's not going to display the number as opposed to the date and now
what I've done here because when I was looking for the date in here
my dates are actually if you click on one of these you're basically a formula
equals D 4 plus 1 and I started with one here which is equal to b1 which is a
date and so that these aren't really dates here and so I had a difficult time
finding the date in there so what I did is I put in it a column in between there
which I hid and so I'm gonna unhide it now where I put another role sorry and
if you click on each of these numbers you have the the serial phone serial
number date for dates for each of them and so what I'm doing in here in my code
is I am look changing the required date to
the serial number and then I'm looking in this row that I've hidden here
underneath so I'm setting range equals e five which is my hidden role here
b-52 Oh X 5 and what I'm looking for is F date which is my serial number form of
the required date and then Excel formulas looking at the whole number
search order X by columns search directions Excel next search format
cross and last equals the range dot column with the column number that
that's basically today - 30 so it's whatever that date is and it's a cone
number that that's displayed in and if they can't find it it will say message
box not found for else it's gonna display where the range column is which
number they're gonna start deleting from and so what I'm doing then I'm going to
set the what el range equals range equal cells six five which is the beginning
which is the beginning of my beginning of my my daddy here six five two cells a
hundred and last and last being equal to the range the column so it's going to
delete from the first of the data to the date that's thirty days before today el
range selects and then I go el range clear contents and then I go inside by
so that's my set how it's going and so what I'm gonna do is I'll put this on
the side here side here so that you can see it run and so we have in here we
have some vacations and some sick days and they were all totaled up here so
what I'm gonna do is I'm going to show it show it to you as it runs and as I
said this this is set up so that when you open it up it would run so I'm gonna
just go run it right through it
so it's gonna leak till 21 call them number 21 and so now if you see here
what it does it has it has deleted everything that is 30 days before today
so that it's all cleaned up and the numbers have changed here as well
because I'm doing a sum if and there's no more sick days in here or normal
vacation days in the first in the first section so that's how you make a
vacation and sick days expire on your scat stuff schedule please subscribe