Combining an IF function with a WORKDAY function

[taffboyo]

Hi Everyone,

I currently have a working formula: =IF(E4="P7",D4+10,IF(E4="P6",D4+15,IF(E4="P5",D4+20,IF(E4="P4",D4+25))))
Where Col D = start date, Col E= Category(relates to how many days the work will take)
and a completion date is generated in Col F. However, the completion date falls short because it does not exclude weekends, I would like to insert the Workday function into the formula to increase the accuracy, but all my attempts so far have only resulted in Err messages, is it possible to combine the Workday function with the formula in its current form? Or do i need to go back to the drawing board?

[daddylonglegs]

So if E4="P7" do you want to add 10 workdays to the date.....or just add 10 days and then move to next workday if a weekend results?

For the former...

=WORKDAY(D4,IF(E4="P7",10,IF(E4="P6",15,IF(E4="P5",20,IF(E4="P4",25)))))

or the latter

=WORKDAY(D4+IF(E4="P7",10,IF(E4="P6",15,IF(E4="P5",20,IF(E4="P4",25))))-1,1)

[taffboyo]

Hi,
Thanks for the quick reply. Yes, if E4 = "P7" then the work would take 10 workdays from the date entered in Col D. I have attempted to use:=WORKDAY(D4,IF(E4="P7,10,IF(E4="P6",15,IF(E4="P5",20,IF(E4="P4",25))))) but it is still returning an Err message.

[daddylonglegs]

Sorry, I missed a quote around P7 - try this corrected version

=WORKDAY(D4,IF(E4="P7",10,IF(E4="P6",15,IF(E4="P5",20,IF(E4="P4",25)))))

format result cell in required date format. Note that WORKDAY doesn't count the first day, so if you want to include D4 in the 10 workdays then change D4 to D4-1

[taffboyo]

That worked like a charm, thanks ever so much, especially for the D4-1, i had not even considered this!

[roomaggoo]

Hi there, I hope no one minds me reactivating this thread!

I'm currently using the below formula to build a simple Gantt chart in Sheets, which ultimately places an x along my timeline based on the start date, duration (in days), and predicted end date of a task within a project:

=if(and(K$5>=$E6,K$5<=$I6),"x","")

From this, I then use conditional formatting to fill the boxes marked with x, which creates a simple bar chart. In this formula, K is the date along the top of my table (January 1st, January 2nd, and so on) and I is the estimated end date of that specific task.

This is working great for me, but ideally I'd like it to exclude weekends and move to the next working day if at all possible. Could anybody kindly help? Thank you in advance!

[vegendas]

Hi there,
I have a similar problem, did you find a solution? Did you post a new thread?
thanks,

[AliGW]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[vegendas]

Hi there,
I have a challenge here. I need to create a table where different deparments can see if they have something to do
I have the following function

=IF(F$1-$B2<=0;"";IF(F$1-$B2=1;"P1";IF(F$1-$B2=2;"P2";IF(F$1-$B2=3;"sz";IF(F$1-$B2=4;"L";IF(F$1-$B2>4;IF(F$1-$B2<10;"T";"");""))))))

Is anyone have an idea how to make this work using only workdays?

B2-> =today()
F1-> arrival date

Many thanks,

[FDibbins]

Hi there,
I have a challenge here. I need to create a table where different deparments can see if they have something to do
I have the following function

=IF(F$1-$B2<=0;"";IF(F$1-$B2=1;"P1";IF(F$1-$B2=2;"P2";IF(F$1-$B2=3;"sz";IF(F$1-$B2=4;"L";IF(F$1-$B2>4;IF(F$1-$B2<10;"T";"");""))))))

Is anyone have an idea how to make this work using only workdays?

B2-> =today()
F1-> arrival date

Many thanks,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.