Skip to Content
Feb 26 at 03:41 PM

Date Range (starting on Sunday) summary quantity amounts


I been ask to make a report here. I have a table that has our parts number, quantity, and date in it. This table is for shipping to our customers. I need to make a report that will summary the shipment quantity to our customers in that week.

For example

Part Number Customer Week 1 Week 2 Week 3 . . . . . Week 52

ABC Customer 1 333 22 32 90

XYX Customer AB 33 0 33 1000

The date is like this:

Part Number: ABC
Customer: Customer 1
Date: 02/27/2023
Quanity: 111
Part Number: ABC
Customer: Customer 1
Date: 02/28/2023
Quanity: 112
Customer: Customer 1
Date: 03/01/2023
Quanity: 110

I need to get formula to check to see if the date range is between Sunday - Saturday if so then add in the quantities to it. Then add in another formula field for week 2 check to the follow week and add in the quantities for that week. And repeat for another week.

Then on the report I need to replace Week 1, Week 2, Week 3 headers with the date that begins on Sunday for that week.

I have something in a cross-tab report but I had totals to it that I need to remove, but wasn't able to. I manage to remove the grand totals.

Any help in getting the formula working here would be greatly appreciated.

Thank you,