Report Development by Last Week/Current Week/Next Week

harper2772

I have several projects built out on separate Smartsheets. Some of the tasks on these projects have durations that extend for longer than a week. I would like to develop reports that show the working time spent for last week, the current week, and next week.

For example, if I have a task that starts on Thursday (this week) and extends to the following Tuesday. I need two days of the duration to display in the "Current Week" report, and two days to display in the "Next Week" report. I cannot seem to figure this out. When I create the filter in the report for the specific weekly dates, none of the time displays in the current week and all four days are in the following week. Thus, skewing my reports.

Can you please provide guidance on how to resolve? Or, perhaps a better way to display time worked on projects during specific last, current, and next week intervals?

Thanks!

Genevieve P.

Hey @harper2772

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?

Genevieve P.

Hey @harper2772

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

harper2772

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

Genevieve P.

Hey @harper2772

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

harper2772

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!

Genevieve P.

Hey @harper2772

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))))))

harper2772

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

Genevieve P.

Awesome! Glad I could help.

harper2772

Genevieve,

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

Thanks,

Nicole

harper2772

I removed sensitive information.

harper2772

Thought you might need to see this as well.

harper2772

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

Genevieve P.

Hey @harper2772

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?

harper2772

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!

Genevieve P.

No problem at all! I'm glad we were able to get this working for you!