Calculate production date VBA

phiyen8902 · Oct 16th 2020

Hi all Experts,

I would like to say thank you in advance and hope you can help me on this. I have a data and need to calculate the production capacity per day, but manual breaking takes lots of time and typo. So I hope you can help me for vba code (please the attached file for clearly). Thanks again.

**royUK** · Oct 16th 2020

I don't think your end dates are correct. You say that work days are Monday to Saturday, but you include Sundays in some production runs to arrive at your date.

You don't need VBA to calculate this, Excel has a Function. To add days to a date while excluding specific days you can use the WORKDAY.INTL function

=WORKDAY.INTL(F2,ROUNDUP(D2/E2,0),17)-1

WORKDAY.INTL function

phiyen8902 · Oct 16th 2020

Hi RoyUK,

Sorry for wrong calculation because of manually update. Your formula worked perfectly, however I also wish the data can break into 5 rows if the fty products in 5 days (same as the format from col I to col O). Thank you in advance.

**royUK** · Oct 16th 2020

Why do you want it splitting like that. You have an efficient formula that gives you the required information.

phiyen8902 · Oct 16th 2020

Hi RoyUK,

Because I need to calculate the capacity for each day. For example, Item1AA, line 21 can product 650pcs on 8-Oct, line 32 can product 550pcs on 8-Oct => the total capacity of that day is 650+550=1200pcs. And I dont know how to calculate without separate rows

Calculate production date VBA

Participate now!

Share