Hi everyone, I'm trying to make a schedule v...

(Mark Tuckey) #1

Hi everyone,

I’m trying to make a schedule view of the current jobs of the week and who is assigned to where on what day.

I currently use for example (Monday):

=IF(WEEKDAY([Clean Date])=2, [_Cleaners - Clean] , IF(WEEKDAY([Repair Date])=2, [_Repairers - Repair], IF(WEEKDAY([Paint Date])=2, [_Painters - Paint]," ")))

but how would I write it if I only wanted it to show this current week and not every other week as well overlapping?

I will attach a photo of what it is currently showing.

(Suvrutt Gurjar) #2

Hi @Mark_Tuckey,

I believe based on your description, you are currently getting all dates on a day where weekday matches that date.

if it is so,I believe, you may wish to try something like below

=IF(AND(WEEKNUM([Clean Date])=WEEKNUM(Today()),WEEKDAY([Clean Date])=2), [_Cleaners - Clean] , IF(AND(WEEKNUM([Repair Date])=WEEKNUM(Today()),WEEKDAY([Repair Date])=2), [_Repairers - Repair], IF(AND(WEEKNUM([Paint Date])=WEEKNUM(Today()),WEEKDAY([Paint Date])=2), [_Painters - Paint]," ")))

This expression assumes week starts on a Sunday.

(Aleksi Alkio) #3

You should add the year as well… IFS( AND( WEEKNUM([Clean Date])=WEEKNUM(Today()), YEAR([Clean Date])=YEAR(Today()), WEEKDAY([Clean Date])=2), [_Cleaners - Clean], …

(Suvrutt Gurjar) #4

Hi @Aleksi_Alkio, Your insight is valuable as always. Adding the year, makes the expression perfect to work in all conditions.

(Mark Tuckey) #5

@Aleksi_Alkio @Suvrutt_Gurjar thanks guys that worked perfectly.