Solved Calculate which dates for Workdays for every 27 items

Microsoft Excel 2010 - complete product...
October 31, 2013 at 08:50:31
Specs: Windows 7
I have an Excel 2010 worksheet that has all my inventory. I want to create a plan to count my inventory. I need a formula to show what days each item will be counted. I have 1104 items to count and I am counting 27 per day. If I start my counts on 12/30/13 I want the spreadsheet to calculate the date to count each item and eliminate weekends and holidays. These items will be counted 6 times per year, but I am only looking for the 1st count date for each item. Is this even possible?

See More: Calculate which dates for Workdays for every 27 items

October 31, 2013 at 09:38:25
An example of how your data is set up would be helpful.

Read this How-To which explains the use of pre tags
and post a small sample of what your sheet looks like.

I need a formula to show what days each item will be counted

Does the inventory need to be counted in a specific order?
or just the first 27 day 1, the next 27 day 2, etc. etc.?

The more info you can supply the better.


Report •

October 31, 2013 at 16:56:11
✔ Best Answer
Mike is correct is asking for details about your data layout, but just for fun I tried something and it might work for you.

Assuming your data looks like this, use the formula shown in B3 and drag it down. If your "Items" start in a different Row, you will have to change the ROW()-2 part of the formula to whatever works for you.

The date in Column B should change every 27 items and skip Saturday and Sunday dates.

          A                   B
1    Item #/Name             Date
2      1/Name 1             12/30/13
3      2/Name 2             =IF(MOD(ROW()-2,27)=0,WORKDAY(B2,1),B2)
4      3/Name 3             =IF(MOD(ROW()-2,27)=0,WORKDAY(B3,1),B3)  

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

November 1, 2013 at 11:36:34

Thanks for the ideas. I got pulled off to a different project until next week. I will try your solution. If it doesn't work for what I need I will post my data.


Report •
Related Solutions

Ask Question