1 Reply Latest reply: May 15, 2014 10:09 AM by Data Kruncher

# Calculated Field returning week beginning.

I have a problem relating to grouping dates and wanted some much needed advice.

I have a extracted dates from a report and all is fine however i now need to compare data by week vs another report that i have.

This would be much easier if i were to group dates in the same way that i receive this other report so that i can make a direct comparison.

Therefore i need to group may existing data in clusters of 7 days.

Example i want to report numbers between 20DEC10 and 26DEC10 dates inclusive and 27DEC10 and 3JAN10 etc.

The week number is irrelevant but want to return the week beginning date so that all dates within 20DEC10 and 26DEC10 have a new calculated field that returns 20DEC10. The following week will return 27DEC10 for all dates within 27DEC10 and 03JAN10 etc.

Help on this would be very much appreciated.

Regards and thanks!!

• ###### Calculated Field returning week beginning.

If RptDate is the date in question extracted from the report, then use this formula for a calculated Date field perhaps named WeekOf:

If(Weekday(RptDate)<2,

RptDate-7-Weekday(RptDate)+2,

RptDate-Weekday(RptDate)+2)[/code]

Monday is weekday number 2 by default.

HTH.