Generating "Week of MM/DD/YYYY" to group by 3

[agray123]

I have data for a job requisition that will constantly be open.

I need to group this by week so managers can be updated weekly on who applied. If I group by week, I get Sunday's date...which is fine, but I would like to create a formula to read the date from the Monday-Friday period (when all applications are being tied to the requisition).

I cna then include this in the group header so it will look like this:

Applications received for the week of June 9-13, 2003.

Data

pg break

Applications received for the week of June 16-20, 2003.

 

[Naith]

Place this in your group header:

"Applications received for the week of " + ToText(
DateAdd('d',-(DayOfWeek({Your.Date})) +2,{Your.Date})
) + " to " + ToText(
DateAdd('d',-(DayOfWeek({Your.Date})) +6,{Your.Date})
)

I didn't code according to the June 9-13 style, because what do you do if your week splits over a month or month&year end?

Naith

 

[Madawc]

If I've understood you correctly, you group by week, but want the group to be called something else.

You can put a formula field in the group header in place of the actual name.

The format you want can be achieved by a mix of DatePart, DateAdd and Totext. Add 1 to Sunday to get Monday,
DateAdd("d", +1, {your.date}).
Add 5 to get Friday, keep both

You'd have to assemble the parts of the date. Something like
"Applications received for the week of "
& Totext(Monday_Date, "MMMM&quot
& datepart ("d", Monday_Date) & "-"
& datepart ("d", Friday_Date) & ", "
& datepart ("yyyy", Monday_Date)

Madawc Williams
East Anglia, Great Britain

 

[agray123]

thanks to both of you for the assistance....Naith, your formula worked, however I am getting a time value in there as well..

Applications received for the week of MM/DD/YYYY 12:00:00 AM

How do I eliminate this?

 

[Naith]

Try wrapping the 2nd and 4th lines of the formula with CDate().

i.e: CDate(DateAdd('d'...etc, etc...+2,{Your.Date}))

Naith

 

[agray123]

Done...Excellent!