Possible to Display different report based on day of week in dashboard widget

gwilkinson

I currently use smartsheets for weekly assignments for my staff. We have a group of reports setup to show a summary of their assignments daily. What I would like to know/accomplish is whether I can format a report that handles what the 7 we currently use all in one? To give some context I am reworking our dashboard to show a widget that has their assignments for that day. So I either need to…

1- Figure out how to just show a weekday assignment in a single report to display.

2- Figure out how to cycle reports through the widget based on the day of the week.

Attached are some images of what we currently have and my work in progress.

Here is the active schedule

This is the current single day report data for the current user. We have 6 others for each day of the week.

My work in progress. But alas, it just displays the whole week. Is this doable in a single report?

I hope I was clear enough… I am about 3 months in learning smartsheets so any guidance is helpful!

Georgie

Hi @gwilkinson,

The issue here is that the WEEKDAY Function works with Sunday equalling 1, Monday equalling 2, and so on. Therefore, the formula you’re currently using is set up with the incorrect numbers. 

To explain further, you saw the values in the screenshot you posted on April 3rd, which was a Thursday, so the weekday function saw “TODAY” as a value of 5, and your current formula returned the value from the Friday column since it states “IF(WEEKDAY(TODAY()) = 5, Friday@row”.

To correct this, you can increase each number in the formula by 1, so your formula should be as follows:

• =IF(WEEKDAY(TODAY()) = 2, Monday@row, IF(WEEKDAY(TODAY()) = 3, Tuesday@row, IF(WEEKDAY(TODAY()) = 4, Wednesday@row, IF(WEEKDAY(TODAY()) = 5, Thursday@row, IF(WEEKDAY(TODAY()) = 6, Friday@row, IF(WEEKDAY(TODAY()) = 7, Saturday@row, Sunday@row))))))

Does that work?

Georgie

gwilkinson

Anyone able to help on this? It's got me quite stuck.

Adam Murphy

I would use a helper column for the "Daily Task" that uses a nested IF statement to fill in the value for the given day of the week (Something like this: =IF(WEEKDAY(TODAY()) = 1, Monday@row, IF(WEEKDAY(TODAY()) = 2, Tuesday@row, IF(WEEKDAY(TODAY()) = 3, Wednesday@row, IF(WEEKDAY(TODAY()) = 4, Thursday@row, IF(WEEKDAY(TODAY()) = 5, Friday@row, IF(WEEKDAY(TODAY()) = 6, Saturday@row, Sunday@row))))))

gwilkinson

Maybe I am not using this right… Should this formula fill the cell with a number 1-7? Should anything populate in this column? I used this and nothing shows up… If I click the cell it shows me your formula in a multi-colored format…

I appreciate your help!

gwilkinson

I got the helper columns working.

How might I go about this filtering? I seem to only be able to get filters to still show EVERYTHING… Or nothing.

Adam Murphy

Create a report showing this new field, which should always be that day's tasks. Isn't that what you wanted?

gwilkinson

No matter the filtering I use in the report, it just shows everything

gwilkinson

I Finally figured this out! Thank You.

Georgie

Hey @gwilkinson, glad to hear you worked this out!

Would you be able to share what you did to help other members reviewing this thread?

Thanks
Georgie

gwilkinson

I ran into an odd situation where the formula I am using is not working in some cells. I tried using this as a column formula (How I would prefer). I also tried it as a cell formula. But I have random rows where the formula simply refuses to work.

This is the formula in question. It should fill in the Additional assignment column with the assignment for the given day. But it's randomly not filling in the info.

''=IF(WEEKDAY(TODAY()) = 1, Monday@row, IF(WEEKDAY(TODAY()) = 2, Tuesday@row, IF(WEEKDAY(TODAY()) = 3, Wednesday@row, IF(WEEKDAY(TODAY()) = 4, Thursday@row, IF(WEEKDAY(TODAY()) = 5, Friday@row, IF(WEEKDAY(TODAY()) = 6, Saturday@row, Sunday@row))))))''

Georgie

Hi @gwilkinson,

The issue here is that the WEEKDAY Function works with Sunday equalling 1, Monday equalling 2, and so on. Therefore, the formula you’re currently using is set up with the incorrect numbers. 

To explain further, you saw the values in the screenshot you posted on April 3rd, which was a Thursday, so the weekday function saw “TODAY” as a value of 5, and your current formula returned the value from the Friday column since it states “IF(WEEKDAY(TODAY()) = 5, Friday@row”.

To correct this, you can increase each number in the formula by 1, so your formula should be as follows:

• =IF(WEEKDAY(TODAY()) = 2, Monday@row, IF(WEEKDAY(TODAY()) = 3, Tuesday@row, IF(WEEKDAY(TODAY()) = 4, Wednesday@row, IF(WEEKDAY(TODAY()) = 5, Thursday@row, IF(WEEKDAY(TODAY()) = 6, Friday@row, IF(WEEKDAY(TODAY()) = 7, Saturday@row, Sunday@row))))))

Does that work?

Georgie

gwilkinson

Yes, I figured it out with some help in another thread. I had two helper columns working against each other. I had mistakenly set one with Sunday as first day or week and the other with Monday as first day. I got them bothing working together and it's solved now. Thank you!