How to display a date field in a 4 weekly cycle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have this field [EventDate], which I use to enter data on a daily basis. I
would like to know how to display this data, in a 4 weekly basis, instead of
a daily way or a monthly cycle.
I have managed to display those dates on a monthly basis as follows:
MonthlyDate: (Format([EventDate],"mmm yy"))
Could I get similar expression to display on a 4 weekly cycle?
Any help appreciated.
 
Hi,



Get the difference in week modulo 4. Example


DateDiff("ww", #1/1/1900#, now() ) MOD 4




The anchoring date, here First of January 1900, will define which weeks are
weeks 0, but otherwise, is irrelevant. Use your date field name instead of
Now().


Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Thank you for your help.
I am not sure that this is what I am looking for. What I have in mind is
that the values for each period will be the result of the addition of the
values between the first date and 4 weeks later, and so on. This will display
the point dates as follows:
First period was 20/06/05, which will contain data for the 4 following weeks.
Second periond will be starting on the 11/07/05....
Third period: from 08/08/05...
Perhaps I am too ambitious!
Thanks for any help.
Chris.

Michel Walsh said:
Hi,



Get the difference in week modulo 4. Example


DateDiff("ww", #1/1/1900#, now() ) MOD 4




The anchoring date, here First of January 1900, will define which weeks are
weeks 0, but otherwise, is irrelevant. Use your date field name instead of
Now().


Hoping it may help,
Vanderghast, Access MVP

Chris said:
Hi,

I have this field [EventDate], which I use to enter data on a daily basis.
I
would like to know how to display this data, in a 4 weekly basis, instead
of
a daily way or a monthly cycle.
I have managed to display those dates on a monthly basis as follows:
MonthlyDate: (Format([EventDate],"mmm yy"))
Could I get similar expression to display on a 4 weekly cycle?
Any help appreciated.
Click to expand...
Click to expand...
 
Hi,



OK do. Try:


GROUP BY CDate( (+1) + 28.0 * int(int(YourDate -1)/28 ))



Since a date (and time) is stored in number of days since 1899-dec-30 (for
dates later than that date), so the 2005.10.28 is day 38653. And given 4
weeks is exactly 28 days,

28*int( dateAsNumberOfDay / 28 )

"aggregate" a sequence of 28 consecutive dates to the single date starting
the sequence. The Offset I used, -1 and +1, insure us that the sequence
start a Sunday. Use 0 for a Saturday.

? CDate( (+1) + 28.0 * int(int(Date -1)/28 )) ' actual sequence
2005.10.16
? CDate( (+1) + 28.0 * int(int(#10-16-2005# -1)/28 )) ' is 16 Oct 2005 in
the sequence?
2005.10.16
? CDate( (+1) + 28.0 * int(int(#10-15-2005# -1)/28 )) ' is the 15th in the
sequence?
2005.09.18


shows that we are actually in the sequence that started the 16th of October,
and the previous sequence started the 18th of September.

You can use different offset to shift the starting week ( example, -8 and
+8, or -15 and +15, or -22 and +22, all those keeping a Sunday as a
starting day).

Hoping it may help,
Vanderghast, Access MVP



Chris said:
Hi Michel,

Thank you for your help.
I am not sure that this is what I am looking for. What I have in mind is
that the values for each period will be the result of the addition of the
values between the first date and 4 weeks later, and so on. This will
display
the point dates as follows:
First period was 20/06/05, which will contain data for the 4 following
weeks.
Second periond will be starting on the 11/07/05....
Third period: from 08/08/05...
Perhaps I am too ambitious!
Thanks for any help.
Chris.

Michel Walsh said:
Hi,



Get the difference in week modulo 4. Example


DateDiff("ww", #1/1/1900#, now() ) MOD 4




The anchoring date, here First of January 1900, will define which weeks
are
weeks 0, but otherwise, is irrelevant. Use your date field name instead
of
Now().


Hoping it may help,
Vanderghast, Access MVP

Chris said:
Hi,

I have this field [EventDate], which I use to enter data on a daily
basis.
I
would like to know how to display this data, in a 4 weekly basis,
instead
of
a daily way or a monthly cycle.
I have managed to display those dates on a monthly basis as follows:
MonthlyDate: (Format([EventDate],"mmm yy"))
Could I get similar expression to display on a 4 weekly cycle?
Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Hi,



OK do. Try:


GROUP BY CDate( (+1) + 28.0 * int(int(YourDate -1)/28 ))



Since a date (and time) is stored in number of days since 1899-dec-30 (for
dates later than that date), so the 2005.10.28 is day 38653. And given 4
weeks is exactly 28 days,

28*int( dateAsNumberOfDay / 28 )

"aggregate" a sequence of 28 consecutive dates to the single date starting
the sequence. The Offset I used, -1 and +1, insure us that the sequence
start a Sunday. Use 0 for a Saturday.

? CDate( (+1) + 28.0 * int(int(Date -1)/28 )) ' actual sequence
2005.10.16
? CDate( (+1) + 28.0 * int(int(#10-16-2005# -1)/28 )) ' is 16 Oct 2005 in
the sequence?
2005.10.16
? CDate( (+1) + 28.0 * int(int(#10-15-2005# -1)/28 )) ' is the 15th in the
sequence?
2005.09.18


shows that we are actually in the sequence that started the 16th of October,
and the previous sequence started the 18th of September.

You can use different offset to shift the starting week ( example, -8 and
+8, or -15 and +15, or -22 and +22, all those keeping a Sunday as a
starting day).

Hoping it may help,
Vanderghast, Access MVP




Chris said:
Hi Michel,

Thank you for your help.
I am not sure that this is what I am looking for. What I have in mind is
that the values for each period will be the result of the addition of the
values between the first date and 4 weeks later, and so on. This will
display
the point dates as follows:
First period was 20/06/05, which will contain data for the 4 following
weeks.
Second periond will be starting on the 11/07/05....
Third period: from 08/08/05...
Perhaps I am too ambitious!
Thanks for any help.
Chris.

Michel Walsh said:
Hi,



Get the difference in week modulo 4. Example


DateDiff("ww", #1/1/1900#, now() ) MOD 4




The anchoring date, here First of January 1900, will define which weeks
are
weeks 0, but otherwise, is irrelevant. Use your date field name instead
of
Now().


Hoping it may help,
Vanderghast, Access MVP

Chris said:
Hi,

I have this field [EventDate], which I use to enter data on a daily
basis.
I
would like to know how to display this data, in a 4 weekly basis,
instead
of
a daily way or a monthly cycle.
I have managed to display those dates on a monthly basis as follows:
MonthlyDate: (Format([EventDate],"mmm yy"))
Could I get similar expression to display on a 4 weekly cycle?
Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Hi,



OK do. Try:


GROUP BY CDate( (+1) + 28.0 * int(int(YourDate -1)/28 ))



Since a date (and time) is stored in number of days since 1899-dec-30 (for
dates later than that date), so the 2005.10.28 is day 38653. And given 4
weeks is exactly 28 days,

28*int( dateAsNumberOfDay / 28 )

"aggregate" a sequence of 28 consecutive dates to the single date starting
the sequence. The Offset I used, -1 and +1, insure us that the sequence
start a Sunday. Use 0 for a Saturday.

? CDate( (+1) + 28.0 * int(int(Date -1)/28 )) ' actual sequence
2005.10.16
? CDate( (+1) + 28.0 * int(int(#10-16-2005# -1)/28 )) ' is 16 Oct 2005 in
the sequence?
2005.10.16
? CDate( (+1) + 28.0 * int(int(#10-15-2005# -1)/28 )) ' is the 15th in the
sequence?
2005.09.18


shows that we are actually in the sequence that started the 16th of October,
and the previous sequence started the 18th of September.

You can use different offset to shift the starting week ( example, -8 and
+8, or -15 and +15, or -22 and +22, all those keeping a Sunday as a
starting day).

Hoping it may help,
Vanderghast, Access MVP




Chris said:
Hi Michel,

Thank you for your help.
I am not sure that this is what I am looking for. What I have in mind is
that the values for each period will be the result of the addition of the
values between the first date and 4 weeks later, and so on. This will
display
the point dates as follows:
First period was 20/06/05, which will contain data for the 4 following
weeks.
Second periond will be starting on the 11/07/05....
Third period: from 08/08/05...
Perhaps I am too ambitious!
Thanks for any help.
Chris.

Michel Walsh said:
Hi,



Get the difference in week modulo 4. Example


DateDiff("ww", #1/1/1900#, now() ) MOD 4




The anchoring date, here First of January 1900, will define which weeks
are
weeks 0, but otherwise, is irrelevant. Use your date field name instead
of
Now().


Hoping it may help,
Vanderghast, Access MVP

Chris said:
Hi,

I have this field [EventDate], which I use to enter data on a daily
basis.
I
would like to know how to display this data, in a 4 weekly basis,
instead
of
a daily way or a monthly cycle.
I have managed to display those dates on a monthly basis as follows:
MonthlyDate: (Format([EventDate],"mmm yy"))
Could I get similar expression to display on a 4 weekly cycle?
Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calling dates from two fields... IIf - IsNull help. 1
How do I create a weekly transaction query? 7
How do create a query that groups by weeks? (not a report) 2
Finding a date for the start date of a week number ("ww") 4
Week starts on Friday 2
Month, DATE, Week 7
Average of Cycle Time Per Month 9
Date range for a report 3

Back
Top