Date Calculation + 5 / 20 working days depending on what is within a cell

[digbee]

Hi Guys,

Looking for your help again.

In column 5 I have a formula that calculates the 5 working day deadline based from the date inputted into column b.

However I am now being asked to change this so that.

Column F calculates from the date in Column B either 5 working day if column d states Stage 1 or 20 working day if Column D says Stage 2.

Can anyone help?

Thank you so much.

Jodie

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Pete_UK]

It looks like you have been a member of the forum for 18 months or so, so you should know that thread titles are very important here - they should describe concisely what you are trying to achieve. Please amend yours to comply with the Forum Rule #1 (which you can see in the drop-down at the top of the page).

Also, it helps if you attach a sample Excel workbook, and the yellow banner at the top of the screen describes how to do this.

Hope this helps.

Pete

[Pete_UK]

Thanks for changing the thread title.

Did you see the second part of my post, about attaching a sample workbook?

Pete

[digbee]

Hi Pete, yes I did I am currently remove 'sensitive' information from the sheet prior to sharing.

[digbee]

Please see attached sample spreadsheet,

As previously explained, in Column F I need it to calculate either 5 working days or 20 working days depending on what Column D states
Stage 1 - 5 working days from date received (column b)
Stage 2 - 20 working day from date received (column b)

any help will be greatly appreciated.

[AliGW]

Try this:

=WORKDAY(B5,IF(D5="Stage 1",5,20),'Drop Down Lists'!$A$2:$A$5)

[digbee]

Hi thank you for that, what I was looking for was column F to automatically populate 5 working days from the date in b5 if you select "Stage 1" and then if you took out stage 1 and lets say selected "Stage 2" it would calculate the date in Column F as 20 working days from the date in column b. I hope this makes sense.

Thanks again.

[Glenn Kennedy]

In f5, copied down;

=IF(A5="","",WORKDAY(B5,IF(D5="Stage 1",5,20),'Drop Down Lists'!$A$2:$A$5))

[digbee]

Thanks Glenn just another query to throw in the mix as last night I was a little frazzled (again), I also want it to calculate 5 working days if enquiry, spso and complaint are selected?

So e.,g

Enquiry, SPSO, Complaint, Stage 1 - 5 working days
Stage 2 - 20 working days

[Glenn Kennedy]

Just swap it round a bit:

=IF(A5="","",WORKDAY(B5,IF(D5="Stage 2",20,5),'Drop Down Lists'!$A$2:$A$5))

is this it??

[digbee]

Superstar again, everything I learn from here I put in a wee notebook and the wealth of knowledge I have gained from this site is amazing.

Thank you again.

Jodie

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

[AliGW]

What Glenn gave you is exactly what I gave you, just with a check for a blank cell added, so I wonder if you did actually try my suggestion???

[digbee]

Apologies if I have upset you. I did try yours and copied and pasted but it did not work, I am not sure why probably my error. Again, not sure why you feel I would just ignore a suggestion as all the help I get on here is invaluable.

[Glenn Kennedy]

Ali, so it is! I, for one, didn't notice. I just assumed that whatever it was, it was wrong!!

[AliGW]

So did the OP, evidently.

Not a problem as such, just a query, as I am somewhat bemused at the rejection of it.

[AliGW]

Hi thank you for that, what I was looking for was column F to automatically populate 5 working days from the date in b5 if you select "Stage 1" and then if you took out stage 1 and lets say selected "Stage 2" it would calculate the date in Column F as 20 working days from the date in column b. I hope this makes sense.

Thanks again.

Yes, it made perfect sense. What was it about te way I presented my suggestion that made you think it hadn't?

[AliGW]

Sorry - I have just seen post #15! I was not looking for an apology (none needed), just an explanation. You see, all you did in your response was restate the requirement - you didn't say whether you'd tried it, what errors you were getting, nothing, so it felt as if you were dismissing it as not suitable. Thanks for clarifying.

[digbee]

Hello, not sure if am breaching any rules reopening this, but management have come back to me to say they now want SPSO as 20 working days? I am not sure how to amend the formula provided by both of you to allow this to calculate?

They are looking for Stage 2 and SPSO to be 20 working days and the remaining to be 5.


=IF(A5="","",WORKDAY(B5,IF(D5="Stage 2",20,5),'Drop Down Lists'!$A$2:$A$5))

[AliGW]

What is SPSO? This, maybe:

=IF(A5="","",WORKDAY(B5,IF(OR(D5="Stage 2",D5="SPSO"),20,5),'Drop Down Lists'!$A$2:$A$5))

[digbee]

apologies SPSO is another criteria they would like added to count 20 working days. I tried to add it in to the formula but it didn't like it. See below.

=IF(A5="","",WORKDAY(B5,IF(D5="Stage 2", "SPSO",20,5),'Drop Down Lists'!$A$2:$A$5))

[AliGW]

This:

=IF(A5="","",WORKDAY(B5,IF(OR(D5="Stage 2",D5="SPSO"),20,5),'Drop Down Lists'!$A$2:$A$5))

[digbee]

Thanks very much. that worked a treat!

[AliGW]

Thanks for the rep.