# "Last week" calculation

#### stephensm

##### New Member
Back again. I now need a calculation for subjects enrolled last week (calendar). I've only been able to get a formula to work that counts subjects enrolled in the last seven days. Is there a way to set this up based on a calendar week? Thanks.

Matt

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### lenze

##### Legend
If in columns, say Date, Name, Class you could use a Pivot Table with NO formulas.

lenze

#### stephensm

##### New Member
Thanks Lenze. Cells H7:H999 contain randomization dates and E17 is today's date.

#### dcamos

##### Board Regular
What kind of last week do you want, Sun-Mon ?

#### stephensm

##### New Member
Sunday through Saturday would be fine....

#### dcamos

##### Board Regular
Are you looking for a count of the dates that are in the last Sun-Sat period?

#### dcamos

##### Board Regular
=SUMPRODUCT((H7:H999<=(E17-MOD(WEEKDAY(E1),7)))*(H7:H999>=(E17-MOD(WEEKDAY(E17),7)-6)))

Should give you a count of the days that are between last Sunday and last Saturday

#### stephensm

##### New Member
Thanks dcamos, your formula seems to have done the trick!

Yes, I was looking for a count of randomization dates that occurred in the last full week. So as of today, how many randomzations took place between 6/8 and 6/14. I've played around with the data a little and it looks like it's working. Thanks again.

Replies
4
Views
235
Replies
3
Views
609
Replies
1
Views
180
Replies
6
Views
138
Replies
0
Views
158

1,195,590
Messages
6,010,611
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back