calculate 24 working days from

[smiler44]

a working day is Monday to Friday. I need to work out what will be 24 working days from a date, say 21.05.13, how do I work out what is 24 working days from this date? Could you help me with the code please?
in this case it is 25.06.13 .
thank you
smiler44

 

[smiler44]

I think I have it. if day zero is Monday, add 32 days, if day zero is Tuesday, Wednesday, Thursday or Friday then add 34 days.
this will five me 24 working days
smiler44

 

[Brianwarnock]

Or you could lookup the WORKDAY function

Brian

 

[ypma]

Using the excel 's workday function could be one of the ways to work out the date of the completion of a project with the start date and the number of working days allocated.

This example is using excel 2010 unable to upload excel 2010 on the forum.

Oops sorry Brian you beat me to it .

 

[smiler44]

Thankyou ypma and Brianwarnock. I need to do this via a macro. I kind of have it sussed but now need to over come having a bank holiday that is within my 24 days as I will need to add another day and then of course this could end up as a Saturday or Sunday so need to add another day or two. I'm sure I can work a way round this such as if this date appears in between this date and that add another day.

not tonight I'll leave if for another day.

smiler44

 

[Brianwarnock]

Been catching up on Dr WHO.
workday includes the provision for holidays and you can execute worksheet functions in VBA. So be careful of reinventing the wheel

Brian

Addendum

I have realised that both Workday and Networkdays are not supported Worksheetfunctions in VBA atleast not in pre 2007 so maybe you are on your own .

 

[smiler44]

Brian, I'm crap at functions. I'm sure anything I ever want to do has already been done so yes i'll try to avoid reinventing the wheel
smiler44

 

[Brianwarnock]

Did you solve your problem. Curious to know why you had to do it in a macro.

Brian

 

[smiler44]

Brian, No, I have not solved the problem. for now as I have not resolved the issue of having a back holiday appear within the 24 days from whenever, the macro is just adding 32 or 34 days and so in effect day 23 is being used. So far people are accepting this but if a manager complains there are a few things we can do. manually amend to day 24, not use my macro and do all the work by hand... wont take long before they decide day 23 is just as good or if i'm feeling generous spend more of my own time looking for a resolution.

I have been asked to do some other stuff so am shelving this for now.
smiler44

 

[smiler44]

ypma, just managed to open your excel file, thank you. I can see what its doing but how does it work? how is =WORKDAY(A2,A3,A4) working and what would this be in a macro?

Thanks smiler44

 

[Brianwarnock]

You did not answer my question as to why the calculation had to be in a macro.

I am on 2002 sp 3 so did not try to open ypma's file but suspect that he is populating those cells from the macro, the answer will then be provided on the Worksheet as is eventually required I assume.

The only difference I would make is to have a named range of cells with my holiday dates then in the cell where I wanted the answer

=Workday(A2,A3,holidays)

Brian

 

[Brianwarnock]

ypma, just managed to open your excel file, thank you. I can see what its doing but how does it work? how is =WORKDAY(A2,A3,A4) working and what would this be in a macro?

Thanks smiler44

I have now opened the file and see that ypma just used Workdays on the sheet with no macro involved, he does give a full explanation so I do not understand your subsequent question. If you are asking how WORKDAY does the calculation then we can only guess as we do not have access to the code.

As I said I would have the function in a worksheet and populate the cells from my macro, which I presume is required to obtain one or both of the datefrom and number of days: the holidays would be in a named range.

Brian