Planning activities on difrent dates - but two activities only per day 1

[MITTENCAT]

Hi all

I have a large spreadsheet with a series of activates on the majority can be done the same day, however further down the process we can only do two each day.

So is there a formula that anyone knows that looks at a row of dates and then only allocates two activities on any one weekday, so it would look like something the screen shot ?

 

[kjv1611]

For starters, I can't get my brain straight on looking at the example. Why is the Date for completion 1 to 4 months before the "Start date"? That just defies all logic, if you know no background. But if you say it's correct in your world, then sure, whatever. I'll try to ignore.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Also, I see you have the two dates, but where are you wanting to allocate it to? Another sheet? Another column? I'm not quite sure I follow that.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MITTENCAT]

Hi,

Sorry it is really hard to explain (to me anyway) but will try

The first date is when a simple task when part 1 of a series of activities will complete, this is a very simple task where many items will be complete on a single day.

Step two, the second date in my screen shot, is where I can only complete two tasks per day. So even if i have 50 task 1's complete i can only complete 2 of these the next day. (not my process I add)

So in column 1 I will have many dates all the same, in column 2 I can only have 2 rows with the same weekday date, dates in the second column are all driven from the dates in column 1

Have I explained myself a little better ?

 

[kjv1611]

OK, so this is a basic queuing system you need to setup. It's basic enough that you may can do this with a few embedded functions in your formula. Basically, the logic would say, "If there are less than 2 instances of THIS DATE in the list, then use THIS DATE, otherwise, use THE NEXT DATE."

So, where do you get THIS DATE or THE NEXT DATE? I don't see that info anywhere.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MITTENCAT]

I am given the dates for task 1, they do change frequently, so I am currently manually moving dates for task 2 as I cant get a formula to work and it's taking hours every week making sure i only have two activities on one workday

Task 2 feeds other dates in a very long process, these are simple next working day dates which work fine

 

[kjv1611]

OK, so here's what I was guessing at, and if sounds correct, it will give a starting possibility. However, if you have a more complex scenario, I start to wonder if we you just need a VBA Function built so you can use that as a formula.

What I'm doing is assuming Column 2's date should attmept to be 1 month after column 1's date. If that's not possible, try 2 months. And you could make it try for a few months that way in case you have say 6 or 8 with the same date. But if it's possible to do more (and maybe it's worth it regardless), it'll be easier to read if you are fine with putting it into a VBA Function. I'm playing with it right now, though, b/c what you really want to do is say "if THIS DATE exists two times already in the ABOVE cells, don't use it again, but instead use the NEXT DATE"... I'll have my idea ready in a few minutes I think..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MITTENCAT]

Thanks really appreciate the help - column 2 should be the next working day after column 1 not a month after

 

[kjv1611]

Well, I had it ready with the month one.. it's basically this.. but I'll change it to the WorkDay formula. It piqued my interest once I thought about it. A few years ago, I was working on a Master's Degree in BI, and part of what we looked at was Queueing and how to do that via VBA. Even simple queue conceps can get tricky quick, depending upon variables. But I think we can have something working here..

Here's the basic idea - just need to swap to Workday function:

=IF(COUNTIF(INDIRECT("C2:C"&(ROW()-1)),EDATE(B3,1))<2,EDATE(B3,1),EDATE(B3,2))

I put that in cell C3 where C2 is the first date for your 'Start Date' column and column B contains the Date of Completion or Complection Date.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

OK, here's what it looks like with the WORKDAY function

The formula:

=IF(COUNTIF(INDIRECT("C2:C"&(ROW()-1)),WORKDAY(B3,1))<2,WORKDAY(B3,1),WORKDAY(B3,2))

In testing with your sample data it never repeats a number. What would be a good test would be to see if you use the same initial date like 10 times what happens?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

If that works, and you need to be more careful counting for Holidays and such, you might want to read over the detail for that function here:

https://support.office.com/en-us/article/workday-function-f764a5b7-05fc-4494-9486-60d494efbf33

Basically, for Holidays, you'll need to supply a list, since all businesses/locals will be somewhat different.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MITTENCAT]

Hi,

Thanks for this and I am sorry I know it is me very poorly explaining and showing what I needed, but I think it is very nearly there...

I am hoping this helps in showing what I want - my wife (No sexist intent in that just she was downstairs and doesn't know what I am trying to do) understood it when I showed her the messages and this example

 

[SkipVought]

Please do not post pictures of working examples. Someone who wants to help you, must also create their own example to work on your problem. Why not make it easy for other members to help you by posting data that can be COPIED, PASTED & WORKED.

Another note. The dates are most likely in display format, dd/mm/yyyy. Best to explain that since the giant share of contributors are in the USA, as is kjv1611.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MITTENCAT]

Thanks for the tip SkipVought, I couldn't see how I uploaded a s/sheet hence the screen shot.

I have hopefully added a spreadsheet with some notes....

 

[SkipVought]

OK, here's my solution.

1) Enter 0 (ZERO) in C1 which is Task 2.

2) Format C1 as CUSTOM and enter "Task 2" as the Type.

3) Formula:
[tt]
C2: =IF((B2-C1)>1,B2+1,IF(COUNTIF(C$1:C1,C1)<2,C1,WORKDAY(C1,1)))
[/tt]
...and copy/paste down.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

oops. pulled the trigger too soon.

Formula:
[tt]
C2: =IF((B2-C1)>1,[highlight #FCE94F]WORKDAY(B2,1)[/highlight],IF(COUNTIF(C$1:C1,C1)<2,C1,WORKDAY(C1,1)))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MITTENCAT]

SkipVought - you are an absolute star ! that works like a charm thank you so much