Need a hand with date calc
On Wed, 4 Mar 2009 20:59:48 -0800 (PST), CurlyDave
wrote:
My appologies, 12/30/39 is a Saturday, I must have entered 1940
originally, also my bad, for the formula provided is a worksheet
formula, not a VBA code.
Here it is again with the proper weekday
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E 1),-1,-2,-3,3,2,1,0))
If you wanted a VBA code to Change E1 to the nearest Saturday, then
perhaps a helper cell would work.
Sub NearestSat()
Dim h As Range
Set h = Range("H1")
h.Value = "=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKD AY
(E1),-1,-2,-3,3,2,1,0))"
Range("E1") = Range("H1").Value
h.ClearContents
End Sub
Thanks Dave, I should have picked that code was a cell formula but
it's an area I've not dabbled in yet.
That's a neat trick with a helper cell, and I've sucessfully applied
that and substituted it for my longer routine. It became somewhat
clearer when I realised E1 was a cell, not a variable!
Cheers - Kirk
|