LinkBack URL
About LinkBacks
Can I have Excel automatically add a value to a cell on a certain day every month? Thanks for your help.
Can I have Excel automatically add a value to a cell on a certain day every month? Thanks for your help.
something like
=if(day(today())=XXX,your expression + 1, your expression)
where XXX is the day on which you want to add 1
your expressionis the formula in the cell such as = a1+b1
not a professional, just trying to assist.....
Thanks Duane. Thats a good starting point but I guess my problem may be a little more complicated by the fact that I have to add a value on the last day of the month, every month, no matter what day of the week or if its the 30th or 31st. In other words, each employee earns 8 hours sick leave for each calendar month worked. So in their cell titled "sick leave earned" I have to add "8" to whatever value is in there already, because these hours accumulate. I'm not a professional either, and I'm just starting to learn to work with dates. I can probably stumble through this with a little more help from anybody. Thanks.
0n what day of the month are those 8 hours added to their total?
I just thought that it may be easier to add the 8 hours at 12:01 am on the first of every month (the day after it is earned) since that date would be consistent, rather than a varying date such as the 30th or 31st. Just a thought.
If the formula I need were written in plain language it would read: "At 12:01 AM on the first of every month add 8 to the total in this cell."
most equations for doing this would add the 8 every time the sheet was recalculated - it sound sto me like the hour calculationmust be based on the # of complete months elapsed since some (presumed) start date.
something like = (month(today() - month(XXXX))*8
where XXXX is the presumed start date
of course since there may be multiple years involved, you need to account for that - unless you can make the equation sufficient for a ytd months
The problem my thinking is running into is:
=if(today="given date",X+8,X)
will excecute and incriment x by 8 depending on how many times it is exicuted on "given date". If one tries to get around that by
IF NOW=specific_second then 1) the sheet needs to be active at specific_second AND a calculation causing event needs to occur during that second.
One method would involve a cell that records "DONE FOR THIS MONTH" , and resets on the 15th or otherwise acts like a static VB variable, but that would be messy.
A VB On_Time routine is the only thing I can think of.
Last edited by mikerickson; 04-14-2007 at 08:51 PM.
I see what you guys mean that Excel would not remember that it has already calculated this and would continue doing it every time the sheet was opened. I'm not into VB yet so I guess I'll have to add these values manually for now. Thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks