Count dates last week, Mon-Fri

[bsl13]

Hello, I am in need of a formula that counts dates that fall last week, Monday through Friday. The week will always change (e.g., every Monday when I pull my data, I need to count dates from the week prior). Is this possible?

[CK76]

Yes. Assuming that you have today's date stored in A1 (i.e. =Today())

Then formula for calculating last Monday.
=A1-WEEKDAY(A1,3)-7

So formula would be something like..

Please Login or Register  to view this content.

[bsl13]

Thank you. So with this formula, I would need to enter in my date range each week?

[CK76]

Without knowing how your data is structured. Can't really comment on that.

I'd recommend uploading sample workbook of how your data is set up, with desensitized data and how/where you want your result to be shown.

To attach a workbook, use "Go Advanced" button found at bottom right of Quick Reply/Edit menu. Then find "Manage attachments" hyperlink and click on it. It will launch new window/tab where you can upload files.

[bsl13]

I have attached the "Desen Report." I am trying to have the weekly submittals on the "RDash" tab to count from the "CanData" tab. Essentially, I will be importing the data into "CanData" tab each Monday and am looking for a formula to count the "RRtoHM Completed Date" (Col L). Thank you for all your help with this!

[CK76]

Something like below then.

If it is between Jul 9 to Jul 13.
In B1:
=DATE(2018,7,16)-WEEKDAY(DATE(2018,7,16),3)-7

Note: For your actual use replace Date() with Today(). Today() function will update to current date.
And formula used will automatically obtain date of previous week's Monday.

Then in Weekly Submittals:
=COUNTIFS(CanData!$J:$J,">="&$B$1,CanData!$J:$J,"<="&($B$1+4),CanData!$B:$B, C3)

Copy to all applicable cells. See attached.

[bsl13]

Thank you so much! This will make life a lot easier!

[CK76]

You are welcome

If this resolves your issue, please mark the thread as solved by using Thread tool found at top of your initial post.