Conditional Statments

[agatan]

Hi All,

I have tried to find solution to this problem for last few days with no success.

I managed to get TodaysDate - 3 days using Today()-3

also I was able to extrct Name of the Day by using  FORMAT([TodaysDate - 3 days].[Date], "dddd")

But any attempts then to build in conditional stetments like justfor example

if ( FORMAT([TodaysDate - 3 days].[Date], "dddd") = "Saturday", TODAY()-4)    

to display date of the Friday preceding that Saturday failed.

Is this possible to build in such statments ?

Hope someone will be able to help.

I have a column that stores “Last Job Start Time” (type: Date/Time).

I want to find out if compliant or non-compliant based on this date.

I want to find out and display the date:

if “Last Job Start Time” < “Todays Date – 3 days “ = non-compliant

else Compliant.

In addition, we do not take into consideration Saturdays and Sundays so

if “Todays Date - 3 days”  = Saturday or Sunday , I want to display date of the preceding Friday

If “Last Job Start Time”   <     that  “date of the preceding Friday” = non-compliant

I have tried :

Column =

IF([Last Job Start Time]<[TodaysDate-3days],"Non-Compliant", 

IF(                                                                                                                                                                                    

IF([TodaysDate-3days].[Day]=[Weekday Name]="Saturday",[TodaysDate-3days]=TODAY()-4),

IF(([TodaysDate-3days]=TODAY()-4)<[Last Job Start Time],"Non-Compliant")     

||

IF([TodaysDate-3days].[Day]=[Weekday Name]= "Sunday", [TodaysDate-3days]=TODAY()-5),   

 IF(([TodaysDate-3days]=TODAY()-5)<[Last Job Start Time],"Non-Compliant")              

    )                                                                                                                                                                                                                                                                                  

Kind Regards,

Agata

[ChrisMendoza · Accepted solution]

@agatan -

I think I understand your issue, possibly below will aid in your solution.

ReAssigned Dates =
IF (
    WEEKDAY (
        Table1[Dates],
        2
    ) = 6,
    Table1[Dates] - 1,
    IF (
        WEEKDAY (
            Table1[Dates],
            2
        ) = 7,
        Table1[Dates] - 2,
        Table1[Dates]
    )
)

[ChrisMendoza · Accepted solution]

@agatan -

I think I understand your issue, possibly below will aid in your solution.

ReAssigned Dates =
IF (
    WEEKDAY (
        Table1[Dates],
        2
    ) = 6,
    Table1[Dates] - 1,
    IF (
        WEEKDAY (
            Table1[Dates],
            2
        ) = 7,
        Table1[Dates] - 2,
        Table1[Dates]
    )
)

[agatan]

@ChrisMendoza 

Hi ,

Thank You for your help.

Thanks to your code I was able to reassign dated (exclude Saturdays and Sundays)!  Great help.

But I am still struggling to compare those two dates.

I want to find out if my  "LAST JOB START DATE " is earlier then "Reassigned Date " .

How to commpare dates ? Statments like  below dont work

IF(Table1[Last Job Start Date] > Table1[ReassignedDate], "Compliant", "Non-Compliant" )

Any suggestions will be highly appriciated 🙂

Kind Regards,

Agata

[ChrisMendoza]

@agatan -

Are you getting some sort of error message? An IF ( ) like what you've mentioned should work as a calculated column.

[agatan · Accepted solution]

@ChrisMendoza 

Hi,

After your reasurment that the IF statment should work, I want to see what can cause the error.

There was few oter small issues but it is working now.

Big Thank You for your support!

Kind Regards,

Agata

[rocky09]

Can you provide some sample data. So, I can try for a solution.

[agatan]

Hi Rokcy09 ,

Please find sample data:

Kind regards,

Agata