Moving Rows Based on the Input of a Date

**Building** · 06-17-2014, 12:54 PM

Hello,

I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.

Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.

Please let me know if I can clarify this, I'm fairly new at Excel.

**natefarm** · 06-17-2014, 02:42 PM

have a second column dependant on the date and move as I change the date.

I don't know what you mean by having a column move. Can you clarify or provide examples?

**Building** · 06-17-2014, 02:49 PM

Originally Posted by natefarm

I don't know what you mean by having a column move. Can you clarify or provide examples?

I probably should have phrased it better.

Say I have a schedule with a framer, electrician, plumber, and a HVAC specialist set to come in one after the other beginning on 6/18/2014. I recieve word that the framer can't come in until 6/19. The framer coming in first is essential because of the steps in the building process so the order of the tradesmen will never change.

I'd like to find a way to format a spreadsheet so when I change the date of the framer to 6/19 the electrician changes to 6/20, the plumber to 6/21,etc. I would also like the format to "wrap" around weekends so the plumber will show up in excel as coming in on 6/23 (because 6/21 is a Saturday).

Hopefully this will give you a background of what I am trying to do; please let me know if I can clarify further.

**natefarm** · 06-17-2014, 03:31 PM

Ok, so for example put 6/19 in A1. In B1 put the following:

Formula:

Please Login or Register  to view this content.

You can drag the formula over to subsequent columns. Of course, there are other factors I'm sure you know you'll have to consider: What if one of the contractors doesn't need a full day, or what if they need multiple days, or what if they are delayed or underestimated. Also, it doesn't handle holidays. It's a start, though.

**Building** · 06-17-2014, 03:44 PM

Originally Posted by natefarm

Ok, so for example put 6/19 in A1. In B1 put the following:

Formula:

Please Login or Register  to view this content.

You can drag the formula over to subsequent columns. Of course, there are other factors I'm sure you know you'll have to consider: What if one of the contractors doesn't need a full day, or what if they need multiple days, or what if they are delayed or underestimated. Also, it doesn't handle holidays. It's a start, though.

I think I see what you are doing with the formula, how do I input text into the formula to have the list of 4 trades move according to the date? We reserve each contractor a full day for work so luckily that won't be an issue.

**natefarm** · 06-17-2014, 03:59 PM

Maybe I'm not understanding. Did you drag the formula over? Enter the framer date in A1 and the formula in B1 (the electrician column), then drag the formula over to C1 (plumber), D1 (HVAC), etc. Isn't that what you want?

**Building** · 06-17-2014, 04:09 PM

Originally Posted by natefarm

Maybe I'm not understanding. Did you drag the formula over? Enter the framer date in A1 and the formula in B1 (the electrician column), then drag the formula over to C1 (plumber), D1 (HVAC), etc. Isn't that what you want?

Ahh now I see what you mean, this is exactly what I'm looking for. Is there a way to "frame" weeks at a quick glance so I can see what is coming up in the next say two weeks? I suppose leaving the weekends in (but excluded from the contractor scheduling) with some sort of formatting to distinguish them from the weekdays would work, is this possible?

Even possibly inserting blank cells for the days over the weekend would provide enough of a visual cue to frame the upcoming schedule, I just don't know how to do this with my limited knowledge.

**natefarm** · 06-17-2014, 04:42 PM

I don't know if I'm on the right track, but the solution I had offered has the columns based on the contractor not the date, and it sounds like you want a second representation based on the date (more like a calendar). The attachment has what I had suggested on sheet1, and something more like a calendar on sheet2 that, on each day, shows the count of contractors from sheet1 that are scheduled that day. Maybe you can take it from there. Hope it helps.