Report Development by Last Week/Current Week/Next Week

Screen captures definitely help - these clarify that you have Dependencies enabled on your sheet. This means that if the row has a Predecessor, the Start and Due dates won't be seen as identical, even if they're the same date on the same row. The Start will be at the start of the day, and the Due/End date will be later on time-wise, making them slightly different.

Here you'll need to clarify in your formula that you're looking at the DATEONLY part of the cell.

See: DATEONLY Function

=IF(AND(DATEONLY(Start@row) = DATEONLY(Due@row), Start@row >= [This week's start date]#, Start@row <= [This week's end date]#), Duration@row, IF(AND(Start@row <= [This week's start date]#, Due@row >= [This week's end date]#), 5, IF(OR(AND(Due@row < [This week's start date]#), Start@row > [This week's end date]#), 0, IF(AND(Start@row > [This week's start date]#, Due@row < [This week's end date]#), NETWORKDAYS(Start@row, Due@row), IF(Start@row > [This week's start date]#, NETWORKDAYS(Start@row, [This week's end date]#), NETWORKDAYS([This week's start date]#, Due@row))))))

Does that now do everything you need it to?

All comments

I would personally create a formula to return the number of working days that each task row has within the current week. The way I would do this is to add the Current Week's Start and End dates into a Sheet Summary panel to easily reference.

Then you can use a nested IF statement to look through all possibilities of date overlaps:

=IF(AND([Start Date]@row <= [This Week's Start Date]#, [End Date]@row >= [This Week's End Date]#), 5, IF(OR(AND([Start Date]@row < [This Week's Start Date]#, [End Date]@row < [This Week's End Date]#), [Start Date]@row > [This Week's End Date]#), 0, IF(AND([Start Date]@row > [This Week's Start Date]#, [End Date]@row < [This Week's End Date]#), NETWORKDAYS([Start Date]@row, [End Date]@row), IF([Start Date]@row > [This Week's Start Date]#, NETWORKDAYS([Start Date]@row, [This Week's End Date]#), NETWORKDAYS([This Week's Start Date]#, [End Date]@row)))))

This should return the Working Days for the current week in a column that you can then use to Filter by in your Report.

You could use another formula in the Start and End dates in the Sheet Summary to automatically return dates based on Today's date, or you could reference a Date Cell in the Grid of the sheet instead, and use a Record a Date workflow to update those dates on a weekly basis.

Cheers,

Genevieve

Thank you! This is an amazing formula and works great! Follow up question. What if the duration is less than 1 full day? For example, if the start and end dates are the same, but I only need 0.25 days to complete a task. Is there a way to allot for just a quarter of a day?

I super appreciate your help!

Best, Nicole

You could add a statement at the beginning that looks to see if the Start/End date are the same day, and if they fall within this week. If that's the case, instead of doing NETWORKDAYS, you would simply return the duration (from wherever that's listed).

=IF(AND([Start Date]@row = [End Date]@row, [Start Date]@row >= [This Week's Start Date]#, [Start Date]@row <= [This Week's End Date]#), Duration@row, IF(AND([Start Date]@row <= [This Week's Start Date]#, [End Date]@row >= [This Week's End Date]#), 5, IF(OR(AND([Start Date]@row < [This Week's Start Date]#, [End Date]@row < [This Week's End Date]#), [Start Date]@row > [This Week's End Date]#), 0, IF(AND([Start Date]@row > [This Week's Start Date]#, [End Date]@row < [This Week's End Date]#), NETWORKDAYS([Start Date]@row, [End Date]@row), IF([Start Date]@row > [This Week's Start Date]#, NETWORKDAYS([Start Date]@row, [This Week's End Date]#), NETWORKDAYS([This Week's Start Date]#, [End Date]@row)))))

Cheers,

Genevieve

Hi there! One more question. If the "Start" date is prior to the current week, it does not seem to count the days worked in the current week. Can you help with this as well? Thank you again! I appreciate your help!

Good catch! I had the wrong reference in one place which caused this. (I referenced the Start date when I should have referenced the End)

I've updated one small section in the middle:

=IF(AND([Start Date]@row = [End Date]@row, [Start Date]@row >= [This Week's Start Date]#, [Start Date]@row <= [This Week's End Date]#), Duration@row, IF(AND([Start Date]@row <= [This Week's Start Date]#, [End Date]@row >= [This Week's End Date]#), 5, IF(OR(AND([End Date]@row < [This Week's Start Date]#), [Start Date]@row > [This Week's End Date]#), 0, IF(AND([Start Date]@row > [This Week's Start Date]#, [End Date]@row < [This Week's End Date]#), NETWORKDAYS([Start Date]@row, [End Date]@row), IF([Start Date]@row > [This Week's Start Date]#, NETWORKDAYS([Start Date]@row, [This Week's End Date]#), NETWORKDAYS([This Week's Start Date]#, [End Date]@row))))))

You are amazing, Genevieve! I can't wait until my meeting is done for me to try this out!

Thank you so much!

Best, Nicole

Awesome! Glad I could help.

Genevieve,

For some reason, I cannot seem to get this to work. Can I share my sheet with you to look at?

Thanks,

Nicole

I removed sensitive information.

Thought you might need to see this as well.

I thought I had figured it out, but I was wrong. I still need help on the above. Thank you in advance!

Here you'll need to clarify in your formula that you're looking at the DATEONLY part of the cell.

See: DATEONLY Function

Does that now do everything you need it to?

That did it! So sorry for all the questions! I genuinely appreciate you and all the help you've given me. Have the most amazing day!