I'm creating a tracking spreadsheet for taking the amount of time taken for us to provide a resource requested.
I have the date the request is opened. Our SLA is 20 working days. (Target date).
So, I have created a formula that gives me the Target date.
=IFERROR(WORKDAY.INTL([@[Request Opened]],20,1,Lists!E14:E57),"N/A") i.e WORKDAY.INTL(Request date),20 days (SLA),holidays)
I have also teamed this with a verbal tracker =IFERROR(IF([@[Target date]]>TODAY(),"On track","Delayed"),"N/A").
However, I'd like to write out a dynamic formula that tells me how many working days are remaining from the current date (today) to the target date. I can do a target date minus current date, but that will not account for the weekends.
Any suggestions? Thanks.
Divisha
Hi Divisha,
You can use the NETWORKDAYS or NETWORKDAYS.INTL function to calculate the number of workdays excluding weekends.
Mynda
For those interested, I ended up with this formula, which did the trick.
Days left
=IFERROR(IF([@Status2]=Lists!$C$3,NETWORKDAYS.INTL(TODAY(),[@[Target date]],1,Lists!$E$2:$E$45),"N/A"),"Request not open")
And Target date status
=IFERROR(IF([@Status2]=Lists!$C$6,Lists!$C$6,IF([@Status2]=Lists!$C$4,"N/A",IF([@Status2]=Lists!$C$5,"N/A",(IF([@[Target date]]>TODAY(),"On track","Delayed"))))),"N/A")
Thanks Mynda
