Macro to add to date in selected cell by one working day

[ExcelFailure]

Afternoon,

I'm after a macro that will bring up a box in which I can enter the number of working days that I want to add to the date in the selected cell. So, the selected cell may contain Monday's date. When I click to run the macro, and put 3 working day's in the box, it should come up with Thursday's date. Alternatively, if it had Friday's date in, and I put 2 in the box, it should say Tuesday's date.

Can anyone help?

Many many thanks

[judgeh59]

what about if you are on Wednesday and you put in 4 and it lands on Sunday?....do you want it to bump to Monday?

[ExcelFailure]

Well, if it's wednesday and I enter 4, then I would want it to go to Tuesday, because that is 4 working days away.

Is it understandable or not? Basically i want to dis-count the weekends

[judgeh59]

excellent, that what I needed...thanks

[ExcelFailure]

cool, will wait to hear

[judgeh59]

sorry it's taking some time....I actually have to do work...go figure....it may be a bit....if somebody else has a faster answer, go for it....again, sorry

[ExcelFailure]

no worries

[judgeh59]

this seems to work on the dates I tried....thanks for your patience...

Please Login or Register  to view this content.

[judgeh59]

you can wrap the whole sub in an IF ISDATE(cell) statement to make sure you are in a cell with a date

[ExcelFailure]

OK, any ideas on how? Just a worksheet event?

Also, I'm assuming this needs a userform?

[judgeh59]

if you are talking about how to launch the macro, then I would add it to the quick access toolbar...you can add macros to the QAT and then click in the date cell, click on the macro icon on the QAT and it will launch the macro....you'll get the input box to ask how many days...and, hopefully, adjust that day correctly....HTH

[judgeh59]

here is the ISDATE code

Please Login or Register  to view this content.

[ExcelFailure]

Excellent thanks.

One minor hitch. When I do 11/09/2013 and add 4 days it goes to 16/09/2013 as opposed to 17/09/2013

Any ideas?

[judgeh59]

try this

Please Login or Register  to view this content.

[ExcelFailure]

Excellent - works perfectly. You're a saint!

[judgeh59]

thanks....I'm glad I could help....and I'm pretty sure I haven't done any actual miracles....but thanks anyway.....

[judgeh59]

Please remember to set the thread to SOLVED...thanks

[6StringJazzer]

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

I will also add that this situation is a perfect one to use the built-in function WORKDAYS, which also allows you to give a list of holidays in addition to taking care of the weekends for you. It saves doing a lot of date arithmetic logic from scratch. It can be called from VBA code using Application.WorksheetFunction.WORKDAY.