# Populate dates based on reference cell

Highlighted
Occasional Visitor

# Populate dates based on reference cell

Hello!

I am working on creating a spreadsheet to track the progress of a project with multiple people starting on different dates.

Essentially what I would like to do would look something like:

 A B C D E F 1 Person Start Date Day 1 Day 2 Day 3 Day 4 2 John 3/1/19 3/1/19 3/2/19 3/3/19 3/4/19 3 complete missed missed ... 4 Jane 5/4/19 5/4/19 5/5/19 5/6/19 5/7/19 5 complete ... ... ...

The project can vary from 50 days-80 days, and I would like to be able to have dates generate themselves rather than having to drag the auto all the way across.

C2-CC2 would be based off reference cell B2

C4-CC4 off B4

etc.

I'm having a difficult time figuring out the how to have the dates generate consecutively in each cell based off the date provided in the reference cell.

Any how-to, advice, or input would be greatly appreciated!

3 Replies
Highlighted

# Re: Populate dates based on reference cell

Having the dates increment by 1 in adjacent cells going across is simple. If you enter the first in Excel's "short date format" you can just add 1 in each subsequent cell.

• So let's say C2 contains 4/28/20 (again, not as text but as an Excel date field)
• Then D2 would simply contain =C2+1 and you copy that across; so E2 contains the formula =D2+1, F2 contains =E2+1, and so on.

An equally important question, though: are you planning to enter words like "Completed" or "Missed" or "Half-day" ....whatever....in each cell in the rows below?

There pretty certainly are easier ways to accomplish what you want to accompllsh. Among other things you could consider: what you are creating in essence is (or appears to be) a combinaton. of a data entry sheet AND a report. Excel is really very good (excellent in fact) in taking raw data (entered in the form of a simple table) and summarizing it in a cross-tabulated report. Noting, for example, hours worked by person by day. Or, taking it up a level, days per week per person. You could make it as granular as desired, as detailed as desired.

Your data entry table could be as simple as a set of columns

DATE, ID, CODE/Hours/Y-N....you'd have to decide. The ID would be some unique identifier for each person; the Code could be "C" for "Complete", "M" for "Missed", etc.--if that's the level of detail you want. Or you could simply not make an entry for days missed, and the software could recognize that for what it means.

It generally is a good idea to think of your INPUT as distinct from your OUTPUT. That's my main point. In doing so, you'd be letting Excel do more of the "heavy lifting." Here it appears (emphasis on appears) that you've basically taken a green ledger sheet paradigm and imposed it on Excel, using it because it has neat columns and rows, and is somewhat automated. That may be an incorrect observation, and I'd happily be corrected....but if it's relatively accurate, I'd truly encourage you to rethink your basic design by looking deeper into what Excel can offer in the way of record keeping and report generation.

Microsoft makes templates available for project management. There are YouTube videos on "how to." You'd benefit, I think, from looking into some of those resources just to become aware of what's possible. It will end up saving you time, more than it'll take to do the learning in the first place.

Highlighted

# Re: Populate dates based on reference cell

@excelstentialdread Another way to generate a range of dates automatically, without the need to copy them across, would be by using the SEQUENCE function, as shown in the attached picture. It requires one extra column, though, where you indicate the duration for the project for each person. Just make sure that the cells that this formula spills into are all formatted as Date.

Highlighted