calculate the first sunday back
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
calculate the first sunday back
During a loop in for next I have a variable myvar (dimensioned as dates), for example:
01/03/2011
...
03/03/2011
05/03/2011
how do I calculate the first Sunday back the date in question?
In this case for all three dates will be the result is:
27/02/2011
01/03/2011
...
03/03/2011
05/03/2011
how do I calculate the first Sunday back the date in question?
In this case for all three dates will be the result is:
27/02/2011
-
- Administrator
- Posts: 78587
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
HansV wrote:Try
myvar - Weekday(myvar) + 1
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
I' sorry but i need to take project in this day!sal21 wrote:HansV wrote:Try
myvar - Weekday(myvar) + 1
I loop the famous dates into the loop ...
And i can have always a pair for each dates a pair of dates with same dd/mm/yyyy but the only distinguisch para is the hh:mm..
similar:
01/01/2011 19.00.14
and
01/01/2011 05.00.11
....
02/01/2011 19.00.27
and
02/01/2011 05.00.02
e need to compare the pair of date and skip from the loop the date with the value most hight respect the little...
in this case i need only
01/01/2011 05.00.11
02/01/2011 05.00.02
i think you understand me.
my part of code:
Code: Select all
...
For Each olItm In olFld.Items
If InStr(olItm.Subject, "test") Then
TEST = olItm.ReceivedTime
ReDim Preserve ARRAY_DATE(I)
ARRAY_DATE(I) = TEST
I = I + 1
End If
Next
I = 0
For I = UBound(ARRAY_DATE, 1) To LBound(ARRAY_DATE, 1) Step -1
For J = LBound(ARRAY_DATE, 1) To I - 1
If ARRAY_DATE(J) < ARRAY_DATE(J + 1) Then
TEMP = ARRAY_DATE(J)
ARRAY_DATE(J) = ARRAY_DATE(J + 1)
ARRAY_DATE(J + 1) = TEMP
End If
Next J
Next I
Me.ComboBox1.Clear
I = 0
For I = LBound(ARRAY_DATE, 1) To UBound(ARRAY_DATE, 1) 'Step -1
ORDINA = ARRAY_DATE(I)
Me.ComboBox1.AddItem Format(ORDINA, "dd/mm/yyyy hh:nn")
Next I
...
in other case please take a eyes if my code is not resonable....
-
- Administrator
- Posts: 78587
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate the first sunday back
Sorry, it's not clear to me what your question has to do with the code.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
ok...HansV wrote:Sorry, it's not clear to me what your question has to do with the code.
not consider it.
compare the 2 dates during the for next and take the date with the minimum date and skip the max date..
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
If is most simple i post my question on other side:HansV wrote:Sorry, it's not clear to me what your question has to do with the code.
Admitting i loop the series of value with a for next how to fill combobox items with the value marked with < - Selected
In effect i need to fill the items oin combo with the dates vale with the oldest time:
...
01/01/2011 05.00.11 <- Selected
01/01/2011 19.00.14
01/01/2011 21.21.11
02/01/2011 05.00.02 <- Selected
03/01/2011 01.00.03 <- Selected
03/01/2011 12.17.21
03/01/2011 18.00.27
...
-
- Administrator
- Posts: 78587
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate the first sunday back
Change the code
to
Code: Select all
For I = LBound(ARRAY_DATE, 1) To UBound(ARRAY_DATE, 1) 'Step -1
ORDINA = ARRAY_DATE(I)
Me.ComboBox1.AddItem Format(ORDINA, "dd/mm/yyyy hh:nn")
Next I
Code: Select all
Dim ORDINA_PREV As Date
For I = LBound(ARRAY_DATE, 1) To UBound(ARRAY_DATE, 1)
ORDINA = ARRAY_DATE(I)
If Int(ORDINA) <> Int(ORDINA_PREV) Then
Me.ComboBox1.AddItem Format(ORDINA, "dd/mm/yyyy hh:nn")
ORDINA_PREV = ORDINA
End If
Next I
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
Hans instead the first back.. how to the first after Sunday based myvar:scratch:?HansV wrote:Try
myvar - Weekday(myvar) + 1
myvar=08/04/2011 Sunday =10/04/2011
myvar=05/04/2011 Sunday =10/04/2011
..
ecc...
Last edited by sal21 on 14 Apr 2011, 07:47, edited 1 time in total.
-
- Administrator
- Posts: 78587
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
I'M STUPID!HansV wrote:How about
myvar - Weekday(myvar) + 8
Tks for patience...
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: calculate the first sunday back
Your code work fine... but i need a distinct date with the oldest time based the same day month and year...HansV wrote:Change the code
toCode: Select all
For I = LBound(ARRAY_DATE, 1) To UBound(ARRAY_DATE, 1) 'Step -1 ORDINA = ARRAY_DATE(I) Me.ComboBox1.AddItem Format(ORDINA, "dd/mm/yyyy hh:nn") Next I
Code: Select all
Dim ORDINA_PREV As Date For I = LBound(ARRAY_DATE, 1) To UBound(ARRAY_DATE, 1) ORDINA = ARRAY_DATE(I) If Int(ORDINA) <> Int(ORDINA_PREV) Then Me.ComboBox1.AddItem Format(ORDINA, "dd/mm/yyyy hh:nn") ORDINA_PREV = ORDINA End If Next I
in thsi case i need:
01/01/2011 05.00.11
02/01/2011 05.00.02
03/01/2011 01.00.03
-
- Administrator
- Posts: 78587
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate the first sunday back
Try this:
Code: Select all
For I = UBound(ARRAY_DATE) To LBound(ARRAY_DATE) Step -1
For J = LBound(ARRAY_DATE, 1) To I - 1
If ARRAY_DATE(J) > ARRAY_DATE(J + 1) Then
TEMP = ARRAY_DATE(J)
ARRAY_DATE(J) = ARRAY_DATE(J + 1)
ARRAY_DATE(J + 1) = TEMP
End If
Next J
Next I
Me.ComboBox1.Clear
Dim ORDINA_PREV As Date
For I = LBound(ARRAY_DATE, 1) To UBound(ARRAY_DATE, 1)
ORDINA = ARRAY_DATE(I)
If Int(ORDINA) <> Int(ORDINA_PREV) Then
Me.ComboBox1.AddItem Format(ORDINA, "dd/mm/yyyy hh:nn")
ORDINA_PREV = ORDINA
End If
Next I
Best wishes,
Hans
Hans