"Last week" calculation

[stephensm]

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

 

[lenze]

How is your data Arranged???
If in columns, say Date, Name, Class you could use a Pivot Table with NO formulas.

lenze

 

[stephensm]

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

 

[dcamos]

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

 

[stephensm]

Sunday through Saturday would be fine....

 

[dcamos]

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

 

[dcamos]

=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]

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.