Hi,

I'm having a bit of trouble getting this right.

A date is entered.

I want to know the closest match that is an absoluite
week from a starting Date of Dec 30 1939

e.g, say the Date entered is 19 Jan 1940 or 21 Jan 1940
the answer I want is 20 Jan 1940.


Any help appreciated
Thanks - Kirk

Will this work?

Sub dt()
MsgBox DateDiff("ww", Range("A2").Value, Range("A3").Value)
End Sub


"kirkm" wrote:

Hi,

I'm having a bit of trouble getting this right.

A date is entered.

I want to know the closest match that is an absoluite
week from a starting Date of Dec 30 1939

e.g, say the Date entered is 19 Jan 1940 or 21 Jan 1940
the answer I want is 20 Jan 1940.


Any help appreciated
Thanks - Kirk

On Mar 1, 8:34*pm, kirkm wrote:
Hi,

I'm having a bit of trouble getting this right.

A date is entered.

I want to know the closest match that is an absoluite
week from a *starting Date of Dec 30 1939

e.g, say the Date entered is 19 Jan 1940 or 21 Jan 1940
the answer I want is 20 Jan 1940.

Any help appreciated
Thanks - Kirk

try this
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E1 ),
1,0,-1,-2,-3,3,2,1))

Check out Dave McRitchie's site

http://www.mvps.org/dmcritchie/excel/datetime.htm

On Sun, 1 Mar 2009 20:30:00 -0800, JLGWhiz
wrote:

Will this work?

Sub dt()
MsgBox DateDiff("ww", Range("A2").Value, Range("A3").Value)
End Sub

Thanks, but I think that'll give the number of weeks between two
ranges ?

On Sun, 1 Mar 2009 21:50:19 -0800 (PST), CurlyDave
wrote:


try this
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E 1),
1,0,-1,-2,-3,3,2,1))

Can you give me a parameter for E1 ?
What are all those numbers ?

Check out Dave McRitchie's site

Yes I had a look... much to take in... as time permits.

I did get something working... but it's pretty rough.

--
Function CfrmDate(ByVal ChosenDate)
Dim a, Diff, x
Diff = DateDiff("d", #12/30/1939#, ChosenDate)
x = Diff / 7
If x < Int(Diff / 7) Then
a = x - Int(x)
x = Int(x)
Select Case a
Case Is = 0.5
' add a day
Do
ChosenDate = DateAdd("d", 1, ChosenDate)
Diff = DateDiff("d", #12/30/1939#, ChosenDate)
x = Diff / 7
Loop Until x = Int(Diff / 7)
Case Else
'subtract a day
Do
ChosenDate = DateAdd("d", -1, ChosenDate)
Diff = DateDiff("d", #12/30/1939#, ChosenDate)
x = Diff / 7
Loop Until x = Int(Diff / 7)
End Select
End If
CfrmDate = ChosenDate
End Function
--


Be good to get it all in one line!

Cheers - Kirk

On Mar 2, 2:04*am, kirkm wrote:
On Sun, 1 Mar 2009 21:50:19 -0800 (PST), CurlyDave

wrote:
try this
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E 1),
1,0,-1,-2,-3,3,2,1))

Can you give me a parameter for E1 ?
What are all those numbers ?

E1 is a date, the formula displays the closest Monday to that date as
that is what
Dec 30 1939 is.

On Mon, 2 Mar 2009 18:39:21 -0800 (PST), CurlyDave
wrote:

On Mar 2, 2:04*am, kirkm wrote:
On Sun, 1 Mar 2009 21:50:19 -0800 (PST), CurlyDave

wrote:
try this
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E 1),
1,0,-1,-2,-3,3,2,1))

Can you give me a parameter for E1 ?
What are all those numbers ?

E1 is a date, the formula displays the closest Monday to that date as
that is what
Dec 30 1939 is.

Very odd, spent quite some time trying to type that in and all I get
is a Compile error, expected: ) and the word YEAR highligted.

On Mar 3, 3:41*am, kirkm wrote:
On Mon, 2 Mar 2009 18:39:21 -0800 (PST), CurlyDave

wrote:
On Mar 2, 2:04*am, kirkm wrote:
On Sun, 1 Mar 2009 21:50:19 -0800 (PST), CurlyDave

wrote:
try this
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E 1),
1,0,-1,-2,-3,3,2,1))

Can you give me a parameter for E1 ?
What are all those numbers ?

E1 is a date, the formula displays the closest Monday to that date as
that is what
Dec 30 1939 *is.

Very odd, spent quite some time trying to type that in and all I get
is a Compile error, expected: ) and the word YEAR highligted.
It's all supposed to be one line, it gets split in the forum.

On Tue, 3 Mar 2009 06:24:20 -0800 (PST), CurlyDave
wrote:

It's all supposed to be one line, it gets split in the forum.

No, No, I reconnected the line ok. Something else is wrong - can't see
what though! Added, removed various brackets, so don't think its
that. I'm using Excel 2002.

Cheers - Kirk

On Mar 3, 9:13*pm, kirkm wrote:
On Tue, 3 Mar 2009 06:24:20 -0800 (PST), CurlyDave

wrote:
It's all supposed to be one line, it gets split in the forum.

No, No, I reconnected the line ok. Something else is wrong - can't see
what though! *Added, removed various brackets, so don't think its
that. I'm using Excel 2002.

Cheers - Kirk

Lets see how you are entering the formula....

On Tue, 3 Mar 2009 22:22:16 -0800 (PST), CurlyDave
wrote:

On Mar 3, 9:13*pm, kirkm wrote:
On Tue, 3 Mar 2009 06:24:20 -0800 (PST), CurlyDave

wrote:
It's all supposed to be one line, it gets split in the forum.

No, No, I reconnected the line ok. Something else is wrong - can't see
what though! *Added, removed various brackets, so don't think its
that. I'm using Excel 2002.

Cheers - Kirk

Lets see how you are entering the formula....

Ok . I can't see if variable e1 is set correctly yet as the
line won't enter.

Sub testDateThing()
Dim e1 As Date
e1 = #12/30/1940#
debug.Print Date(Year(e1),month(e1),day(e1) +
choose(weekday(e1),1,0,-1,-2,-3,3,2,1))
End Sub

Btw when you say 30 Dec 1939 was a Monday,
this gives a Saturday.

? Format(CDate("30 Dec 1939"), "dddd, dd mmmm yyyy. ")

So I'm wrong to assume dddd shows the correct day name
for that Date ?

Thanks - Kirk

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(E1 ),-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

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