Forum Discussion
Conditional Statments
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
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] ) )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
6 Replies
Can you provide some sample data. So, I can try for a solution.
Hi Rokcy09 ,
Please find sample data:
Kind regards,
Agata
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] ) )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
agatan -
Are you getting some sort of error message? An IF ( ) like what you've mentioned should work as a calculated column.
