Hi,
I maintain a Project Tracking sheet that calculates due dates based on project category. I have a Category table on a Data Sheet in my workbook with the list of categories and the number of calendar days allowed for completion. If the due date for the project lands on the weekend (Sat,Sun) or a holiday, then the due date should be the first workday preceding the weekend or holiday. To do this, I have used an IF/THEN statement incorporating the WORKDAY and XLOOKUP formulas which works great if the due date falls on a weekend. However, if the due date falls on a weekday, I have a problem. Here is the formula:
=IF([@[Date Assigned]]="","",WORKDAY([@[Date Assigned]]+[@['# of Days Extended]]+XLOOKUP([@Category],'Data Sheet'!A$2:A$6,'Data Sheet'!B$2:B$6),-1,'Data Sheet'!H$2:H$12))
Is there a better way to do this? I have attached an example workbook.
Thank you, Tracy
Hi Tracy,
If you changed the 'Days for Completion' to only include for workdays, then you could use the WORKDAY function the way it was designed. i.e.:
=WORKDAY([@[Date Assigned]],[@['# of Days Extended]]+XLOOKUP([@Category],'Data Sheet'!A$2:A$6,'Data Sheet'!B$2:B$6))
If that's not an option, you can use this formula:
=IFNA( IF( WEEKDAY([@[Date Assigned]]+[@['# of Days Extended]]+ XLOOKUP([@Category],'Data Sheet'!A$2:A$6,'Data Sheet'!B$2:B$6),2)<6, WORKDAY([@[Date Assigned]]+[@['# of Days Extended]]+ XLOOKUP([@Category],'Data Sheet'!A$2:A$6,'Data Sheet'!B$2:B$6),0,'Data Sheet'!H$2:H$12), WORKDAY([@[Date Assigned]]+[@['# of Days Extended]]+ XLOOKUP([@Category],'Data Sheet'!A$2:A$6,'Data Sheet'!B$2:B$6),-1,'Data Sheet'!H$2:H$12) ), "") Mynda
Thank you! That worked.
