Create workday dates in groups

[jomili]

Okay, weird request. In Column A, I have names: Judy, Delores, Clarence, Kim , Jo Ann, Becky , John. In column D, across from each name, I want to put the workday date (for days Monday through Sunday). I want to be able to add blocks of a week at a time, for the indefinite future, so I'd need to be able to look back at the last date, determine what day of the week it is, and post the next week's names and dates. See the attachment for a little more clarity on this one.

[rylo]

Hi

How aobut

Please Login or Register  to view this content.

rylo

[jomili]

Rylo,

I think you're on the right track, but we're missing the "Workdays" part. The last existing date in my table was 8/30/13, which is a Friday, so the next date should have been 9/2/13, which is the following Monday. The macro, as is, produces entries for 8/31 and 9/1, Saturday and Sunday, which are weekend days, not work days.

[JosephP]

in D37: =WORKDAY(MAX(IF($A$2:$A36=A37,$D$2:$D36)),1)
array-entered and then fill down as need be

[jomili]

JosephP,

That formula seems to do the trick. So all I have to do is copy a block of my existing lines, wipe out the dates in the new block, replace with your formula, format as date, convert the new dates to values, and I'm done. I think that will work. Do I have the context right below to use in a Macro (changing my range, of course)?

Please Login or Register  to view this content.

[JosephP]

yes-looks good to me

[jomili]

Here's my "finished" macro. There are many more columns than I showed in my attachment, so I thought a copy/paste was the easiest solution to filling all the columns. I'd be very please if JosephP or Rylo (or any other bright readers) would suggest ways the macro could be improved, as I really dislike running through the whole sequnce five times. However, I found that when i tried to it all at once, the array formulas all resulted in the same date.

Please Login or Register  to view this content.

[JosephP]

perhaps

Please Login or Register  to view this content.

[jomili]

That Works (except I had to change 29 to 34).

So, earlier, when I first tried doing the whole range, I used this convention:

Please Login or Register  to view this content.

whereas you used

Please Login or Register  to view this content.

So apparently the FillDown works differently than my convention. Why is there a difference?

[JosephP]

your version enters the same formula into all the cells as one array, so all cells return the same value (it's the equivalent of selecting 6 cells, typing the formula and then pressing ctrl+shift+enter). mine enters the array formula into one cell then fills that down

[jomili]

Thanks. That's good information. I always thought both methods worked the same, so were interchangeable. It's good to know they're not, and FillDown should have a place in my toolchest.

[JosephP]

they work the same (except with manual calc) unless it's an array formula so most of the time you can use either

[rylo]

Hi

Next go

Please Login or Register  to view this content.

rylo

[jomili]

Rylo,

Thanks for providing your code. It works exactly as originally requested, and is worthy of further study by me. I'm not planning on using for this application, at least not as is, because there are other considerations I didn't originally post. For instance, my data actually spans 13 columns, but the only change i wanted was in Column 4, per my original post, but all of the columns have to be copied down. As well, my data has a particular font format, and JosephP's version of the macro preserves that, whereas your entry doesn't. But, I'm woefully ignorant when it comes to the "Scripting Object", so I'll puzzle over this code until I can get it to do what I need it do, then evaluate and see which methodology works better.

Thanks,
John