Gadgetman said:
I'm looking for help some help with a formula.
How would I go about a formula for automatically adding 8 hours to a
number each calendar month? I would like to open it and have it done
for
me if that's possible rather than having to remember to add it each
month or forgetting if I did or not.
For instance, i start out with the # 248 in a cell that I would like
to
automatically add 8 to each calendar month.
My solution requires a user defined function - so you need to put code
into a code module. You could then use helper cells, or put everything
into the formula. Your question seems to suggest actually changing the
value -- but I'm going to recommend calculating a number to add based on
today's date, so you're going to need to tell the formula what the
beginning date is. For example, to use only one cell that looks like it
changes automagically, use this formula:
= 248 + ( DateDiff ( "Jan 1, 2010" ) * 8 )
You could also use three helper cells, and have something like this:
= A1 + ( DateDiff ( B1 ) * C1 )
The UDF:
Option Explicit
Public Function MonthDiff( _
Optional ByVal BeginDate As Variant) As Variant
' Return number of months between BeginDate and today
' (Ignores day of month - calculates 1st to 1st)
' BeginDate in future month returns negative number
' BeginDate missing or not a date returns zero
MonthDiff = 0
Select Case VarType(BeginDate)
Case vbDate
MonthDiff = DateDiff("m", BeginDate, Date)
Case vbString
If IsDate(BeginDate) Then
MonthDiff = DateDiff("m", CDate(BeginDate), Date)
End If
End Select
End Function
If you're not familiar with creating UDFs, do this:
right-click on the sheet tab
click on View Code (the VBEditor window will open)
in the code window that opens up copy and paste everything beginning
with Option Explicit including End Function
Because it is user function code, it is fairly easy to install and use:
1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. create a code module for your function:
on the Menu Bar, click Insert | Module
4. paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
When you save the workbook, the user function will be saved with it.
To remove the macro:
1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
(instructions and links borrowed from a post by James Ravenswood)
