Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 2 columns amongst many:
Created Date and Completed Date
All cells under Created Date have an entry, but not all under Completed Date do. I'd like to create a Custom Column with a formula that tells me the number of days (in a 7 day week, not a 5 day working week) between Completed and Created Date; and where no Completed Date exists, between Today's Date (which is dynamic) and the Created Date. Can anyone please help as I can't understand IF formulas.
Solved! Go to Solution.
@Beomere ,
A new column
Total days =
if(not(isblank([Completed Date])), datediff( [Created Date] ,[Completed Date], day), blank())
or
Business Days =
if(not(isblank([Completed Date])), COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created Date],Table[Completed Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)), blank())
got it now, thank you!
@Beomere ,
A new column
Total days =
if(not(isblank([Completed Date])), datediff( [Created Date] ,[Completed Date], day), blank())
or
Business Days =
if(not(isblank([Completed Date])), COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created Date],Table[Completed Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)), blank())
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |