Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a hand with date calc
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calc. an anniversary date(50 years) after marriage date in excel | Excel Worksheet Functions | |||
ND TO CALC FROM ORIG DATE TO NEXT DATE RECEIVED? | Excel Discussion (Misc queries) | |||
y-axis moves from the left hand side to the right hand side | Excel Discussion (Misc queries) | |||
y-axis moves from the left hand side to the right hand side! | Charts and Charting in Excel | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |