First date of week 1

[sschaller]

I created a report that groups employee hours by week. I want a column that says "Week of" and shows the date of the first day of the week (which would always be Sunday). There is not always an entry for Sunday so I can't necessarily pull it from the data source. Is there a formula that can automatically calculate the first date of a week?

 

[dukeslater]

I think a better approach is to create a date table, which can be easily populated with a record for every day from now through whenever. You can include a column that has the corresponding first day of the week for every date, one for fiscal year, fiscal quarter, payroll date, whatever you think you might need.

Then you can just link this table into any reports that need custom date logic.

 

[lbass]

If you group on your date field on change of week, the group name will automatically display the first day of the week (Sunday's date) regardless of whether there is data for that day.

If, however, you want to add "Week of" to the groupname, you would have to:

1) add a text box containing "Week of " to which you add the groupname, or
2) you would have to adjust for the actual data--you would go to the group expert->your date group->options->customize groupname->use a formula to customize name->x+2 and enter:

"Week of "+totext({table.date}-dayofweek({table.date})+1,"MM/dd/yyyy")

-LB

 

[synapsevampire]

If you want a period table as Duke suggests, check out my FAQ for ideas (sample SQL Server table included);

faq767-4532

Otherwise LB's solution works fine.

-k