Date display formula which skips weekends

Hi all,

I have what may seem like an odd request but that's because I've extracted the part I need help automating from a broader setup. I think it's simple enough, though...

Alright, see attached. Here's the situation:

This is a full-year table of weekday dates. The reason why the week counter cycles from 1-8 is because after 8 weeks I need to submit this as part of a larger form (with personal info that I removed). To date (pun intended) i have been manually changing the weeks and days every time I submit, and recently decided the prep a full year's worth of forms all in one sitting. Fun... and a complete was of time.

Here's what I am hoping to get help with: I have set fields for the "Start date" and "End date". I would like the table to use these fields to auto-populate the table. Here are some key factors / caveats / needs

• The start date (B2, C2) will always be a monday
• The end date (E54, F54) will always be a Friday
• The "Day" in column D is always a Monday (caveat below)
• The "Day" in column F is always a Friday (caveat below)
• The 'Month' and 'day' values must 'roll over' as they would in the calendar (i.e when the month changes the day also cycles back - this is one of the bits I don't know how to do... which is why I did it manually)

The one caveat here is that, as you see in yellow, no matter where April 30th lands, IF during the week, it must be the "end" day of the row. Then, the next row should just be "start date = 05.01, and end date = Friday (like normal). Honestly, this is because i have to submit this form on May 1st for any un-submitted dates up to / including April 30 to accommodate an exploding year-end submission deadline. Stupid, i know.

Thanks for your help, in advance. I figure it's actually decently simple, I just wasn't sure how to code the monthly roll-over and the April 30th 'skip to next row' function.

Cheers,


Jay

p.s: the table format needs to be respected - I've cleverly turned a government form into an excel sheet by hacking together screenshot bits around a table and they've allowed me to do it, but 'the form is the form and should i mess with the form of the form I will be bang out of form'.

form. lol

=)