Need a hand with date calc

K

kirkm

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
 
J

JLGWhiz

Will this work?

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

CurlyDave

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
Click to expand...

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
 
K

kirkm

Will this work?

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

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

kirkm

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

Can you give me a parameter for E1 ?
What are all those numbers ?
Check out Dave McRitchie's site
Click to expand...

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
 
C

CurlyDave

Can you give me a parameter for E1 ?
What are all those numbers ?
Click to expand...
E1 is a date, the formula displays the closest Monday to that date as
that is what
Dec 30 1939 is.
 
K

kirkm

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

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.
 
C

CurlyDave

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.
Click to expand...
It's all supposed to be one line, it gets split in the forum.
 
K

kirkm

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

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
 
C

CurlyDave

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
Click to expand...

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

kirkm

Lets see how you are entering the formula....
Click to expand...

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
 
C

CurlyDave

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(WEEKDAY
(E1),-1,-2,-3,3,2,1,0))"
Range("E1") = Range("H1").Value
h.ClearContents
End Sub
 
K

kirkm

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(WEEKDAY
(E1),-1,-2,-3,3,2,1,0))"
Range("E1") = Range("H1").Value
h.ClearContents
End Sub
Click to expand...

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
 
Top