Apr 28 2020 03:30 PM
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!
Apr 28 2020 05:26 PM - edited Apr 28 2020 05:28 PM
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.
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.
Apr 28 2020 09:22 PM
@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.
Apr 29 2020 07:41 AM
I meant to comment yesterday on your user name here: excelstentialdread
What a creative and fun play on words! Well done.
And I'm assuming it was also a relatively faithful representation of how you feel in approaching the use of Excel to carry out your responsibility to track this project.
So I'm also hoping that those of us who have gotten through the various stages of anxiety will be able to make you more comfortable in this community. Microsoft has labeled it a "techcommunity"--which admittedly could be itself a source of dread--but let's hope that before you leave, you will have found it to be a community of acceptance and warmth. :)